Updating multiple values
PDF (US Ltr) - 38.0Mb PDF (A4) - 38.1Mb PDF (RPM) - 37.3Mb HTML Download (TGZ) - 10.2Mb HTML Download (Zip) - 10.3Mb HTML Download (RPM) - 8.9Mb Man Pages (TGZ) - 217.0Kb Man Pages (Zip) - 329.9Kb Info (Gzip) - 3.5Mb Info (Zip) - 3.5Mb My SQL Backup and Recovery My SQL Globalization My SQL Information Schema My SQL Installation Guide My SQL and Linux/Unix My SQL and OS X My SQL Partitioning My SQL Performance Schema My SQL Replication Using the My SQL Yum Repository My SQL Restrictions and Limitations Security in My SQL My SQL and Solaris Building My SQL from Source Starting and Stopping My SQL My SQL Tutorial My SQL and Windows My SQL NDB Cluster 7.5 My SQL 5.7 Secure Deployment Guide option as part of a table reference.This option takes a list of one or more partitions or subpartitions (or both).That will just be a completely unintentional side effect.(Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe.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).You could then do select @value;in order to see what the value was before you updated it My SQL uses Watcom (Oracle) syntax for UPDATE, so it's possible to write something like:update Table1 t1 join Table2 t2 on t1. When done, you "clear" the semaphore by UPDATE table SET value = 0 WHERE name = 'name' The assumption is that the UPDATE is "atomic" in that no concurrent access by another process can occur between testing and setting the value field. test=# create table foo (id int, val int); CREATE TABLE Zeit: 0,837 ms test=*# insert into foo values (1,1); INSERT 0 1 Zeit: 0,434 ms test=*# insert into foo values (2,2); INSERT 0 1 Zeit: 0,298 ms test=*# update foo set val = case when id=1 then 10 when id=2 then 20 end; UPDATE 2 Zeit: 0,424 ms test=*# select * from foo; id | val ---- ----- 1 | 10 2 | 20 (2 Zeilen) Is this okay for you?Andreas -- Really, I'm not out to destroy Microsoft.
If you do not use it, all rows that are outside the range of your updated values will be set to blank![I have posted this in the Flow Control Functions page last year but I still see people asking how to update multiple rows.So, here it is again.]A very server resources friendly method to update multiple rows in the same table is by using WHEN THEN (with a very important note).Ideal when writing a script is just too much effort.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).