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.What I'm doing here is copying the information I need from the row where job_id=1 to the row where job_id=6, on the same table.
(Bug #11758262, Bug #50439) See Section 18.104.22.168, “Determination of Safe and Unsafe Statements in Binary Logging”, for more information.
If you access a column from the table to be updated in an expression, assignments are generally evaluated from left to right.
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).
The solution is surprisingly simple, thanks to My SQL: UPDATE xoops_bb_posts_text SET post_text=( REPLACE (post_text, 'morphix.sourceforge.net', ' the string function REPLACE, all items in the post_text column with 'morphix.sourceforge.net' get this substring replaced by '
Ideal when writing a script is just too much effort.See Section 14.11.7, “Inno DB and FOREIGN KEY Constraints”. Records not matching the equijoin from table2 are marked with null.You cannot update a table and select from the same table in a subquery. This facilitates to update table1 column with expression whose corresponding value from table2 is returned as NULL It took me a few minutes to figure this out, but the syntax for UPDATING ONE TABLE ONLY using a relationship between two tables in My SQL 4.0 is actually quite simple:update t1, t2 set t1.field = t2.value where t1= t2.that; It should be noted that even simple applications of UPDATE can conflict with the 'safe mode' setting of the mysql daemon.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.His example was:update t1,t2 set t1.field=t2.value where t1.this=t2.that; That strikes me as an elegant syntax.This can be done similarily : SET @pos=0; UPDATE busstops SET pos = ( SELECT @pos := @pos 1 ) WHERE route = 1 ORDER BY pos ASCChris H (chansel0049) I experienced a weird issue converting from 4 to 5.