Hi - I'm trying to figure out the best way to update multiple entries in multiple rows in a single table.
the fields are, say, field0, field1, field2, field3, & field4, where field0 is an auto-incrementing primary key.
REPLACE doesn't work in this scenario according to the mysql dev site:
http://dev.mysql.com/doc/mysql/en/REPLACE.html
(bottom of the page)
Which leaves me to dynamically making a query string using either the UPDATE or the INSERT with the ON DUPLICATE KEY UPDATE bit on.
Which is easier, and are there any advantages/disadvatages to either?
the rows that the user will be editing already exist in the table, so my feeling is that using UPDATE will be easier, but as far as I can tell, you have to SET each field entry in each row to the entered variable, which is much more clunky in my mind than the INSERT's VALUE list.
i'd much rather do something like:
UPDATE my_table, (field1,field2,field3,field4) VALUES (a,b,c,d),(e,f,g,h) WHERE prim_key = (0),(1)
than:
UPDATE item
SET field1 = a WHERE prim_key =
AND
SET field2 = b WHERE prim_key =
AND
SET field3 = c WHERE prim_key =
AND
SET field4 = d WHERE prim_key =
AND
SET field1 = e WHERE prim_key =
1
AND
SET field2 = f WHERE prim_key =
1
AND
SET field3 = g WHERE prim_key =
1
AND
SET field4 = h WHERE prim_key =
1
any help much appreciated.
thanks
