Making changes to multiple records based on change of single record with SQL

by

This calls for a complex query that updates many records. But a small change to your data can change things so that it can be achieved with a simple query that modifies just one record.

UPDATE my_table set position = position*10;

In the old days, the BASIC programming language on many systems had line numbers, it encouraged spagetti code. Instead of functions many people wrote GOTO line_number. Real trouble arose if you numbered the lines sequentially and had to add or delete a few lines. How did people get around it? By increment lines by 10! That’s what we are doing here.

So you want pears to be the second item? 

UPDATE my_table set position = 15 WHERE listId=1 AND name="Pears"

Worried that eventually gaps between the items will disappear after multiple reordering? No fear just do 

UPDATE my_table set position = position*10;

From time to time.

More Related Contents:

  1. How can i write in NodeJS with MongoDB the following MySQL Select?
  2. MySQL 8.0 – Client does not support authentication protocol requested by server; consider upgrading MySQL client
  3. MySQL with Node.js
  4. node-mysql multiple statements in one query
  5. nodejs mysql Error: Connection lost The server closed the connection
  6. How do I do a bulk insert in mySQL using node.js
  7. Node.js can’t authenticate to MySQL 8.0
  8. node.js mysql error: ECONNREFUSED
  9. How to auto generate migrations with Sequelize CLI from Sequelize models?
  10. How to access a RowDataPacket object
  11. Retrieve last inserted id with Mysql
  12. How to pass parameters to mysql query callback in nodejs
  13. node.js + mysql connection pooling
  14. connect ECONNREFUSED – node js , sql
  15. How to make join queries using Sequelize on Node.js
  16. NodeJS MySQL Dump
  17. Node.js connect to MySQL Docker container ECONNREFUSED
  18. Use promise to process MySQL return value in node.js
  19. NodeJS responded MySQL timezone is different when I fetch directly from MySQL
  20. Error in MySQL library for Node.js
  21. Which ORM should I use for Node.js and MySQL? [closed]
  22. find number of rows in returned mysql result (nodejs)
  23. MySQL giving “read ECONNRESET” error after idle time on node.js server
  24. Counting associated entries with Sequelize
  25. INSERT INTO fails with node-mysql
  26. MySQL update table based on another tables value
  27. On Duplicate Key Update same as insert
  28. a count for each join – optimisation
  29. The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?
  30. SQL (MySQL) vs NoSQL (CouchDB) [closed]

Leave a Comment