Updating multiple fields in sql
That's how you can do ittable busstopsid | route | busstop | pos1 | 1 | A | 1 2 | 1 | B | 2 3 | 1 | C | 3 4 | 2 | C | 1 5 | 2 | D | 2 6 | 2 | A | 3 7 | 2 | E | 4 8 | 2 | F | 5 9 | 2 | G | 610 | 2 | H | 7Moving D, E, F, G To route 1 SET @pos=(SELECT max(t1.pos) FROM busstops t1 WHERE t1.route = 1 ); UPDATE busstops SET pos = ( SELECT @pos := @pos 1 ), route =1 WHERE id IN (5,7,8,9)I doubt this could be done otherwise since referencing the table you wish to update within the subquery creates circular references After DELETE or UPDATE i.e.
when a row of a subset is lost/deleted/moved away from it, the whole subset will need to be reordered.
I had a problem - a had to update a column "rate" but if the existince or new value is greater then 5 this "5" will be finally value in field.
So, I do it in one "magick" query ;)Here an example:"3" is a some value, from form or somethingupdate item set rate = case when round((rate 3)/2) You sometimes run into the problem that you want to replace a substring occuring in a column with a different string, without touching the rest of the string.
Sometimes you have a lot of processes that could be updating a column value in a table. ID=54321 Here's a workaround for the update/subquery/cant do self table "bug"Senario is, ID 8 has multiple records, only the last (highest) record needs to be changedupdate t1 set c1 = ' NO'where id='8'order by recno desc limit 1I would prefer update t1 set c1=' NO' WHERE ID=8 AND RECNO = (SELECT MAX(RECNO) FROM T1 WHERE ID=8)But that's not currently allowed If you want to update a table based on an aggregate function applied to another table, you can use a correlated subquery, for example: UPDATE table1 SET table1field = (SELECT MAX(table2.table2field) FROM table2 WHERE table1.table1field = table2.table2field)This can be helpful if you need to create a temporary table storing an ID (for, say, a person) and a "last date" and already have another table storing all dates (for example, all dates of that person's orders).
If you want to return the value before you updated it without using a seperate select (which unless you lock the table could return a different value than is updated) then you can use a mysql variable like this:update some_table set col = col 1 where key = 'some_key_value' and @value := col The @value := col will always evaluate to true and will store the col value before the update in the @value variable. Additional information on My SQL correlated subqueries is at UPDATE can apparently be used to implement a semaphore (pardon my pseudocode):while TRUE The code above waits until the semaphore is "cleared" (value = 0) and then "sets" it (value = 1).
Here is a way to use multiple tables in your UPDATE statement, but actually copying one row values into the other, meaning, we're using the same table: UPDATE jobs AS to Table, jobs AS from Table SET to Table.job_type_id = from Table.job_type_id, to Table.job_company_id = from Table.job_company_id, to Table.job_source = from Table.job_source, WHERE (to Table.job_id = 6) AND (from Table.job_id = 1)--------------Pretty cool.
If UPDATE gives an error like this:"You are using safe update mode and you tried to update a table without.."..it may be that your file must be edited to disable safemode. In order for the change in the file to take effect, you must have permission to restart mysqld in the server OS environment.
A table that contains entries of different categories, in which an internal order needs to represented ( lets say a table with busstops on different routes).
If you add new entries or move stops from one route to another you will most likely want to increment the position of the busstop within this route.
If you do not use it, all rows that are outside the range of your updated values will be set to blank!
If you wish to use an increment based on subset of a table you may combine UPDATE with Variables:e.g.
Search for updating multiple fields in sql:
Adam Boyle's commment above was just what I was trying to do, update one table based on a relationship between that table and another.