1

This question is somewhat about "best practices", but also a search for potential problems. I would like to be able to run an update on multiple fields and assign different values without running multiple queries and not using a super complex query. So, what I've done is created a table with a primary key and the "name" column as a unique key.

Now, when I want to update multiple columns with different values, I can run a query like this:

INSERT INTO my_table (name, description) VALUES ('name', 'mydescription'), ('name2', 'description2') ON DUPLICATE KEY UPDATE description = VALUES(description)

Is this a bad idea? Is there a better way to do this? Are the standards police going to come arrest me?

Edit: I did just notice one potential issue with this, being a race condition. If one user removes a row while another user is editing it and they save the information, the edit will recreate the row. (Which could be used as a feature or a bug.)

teynon
  • 7,540
  • 10
  • 63
  • 106
  • See also [Performance question: ON DUPLICATE KEY UPDATE vs UPDATE (MySQL)](http://stackoverflow.com/questions/5036051/performance-question-on-duplicate-key-update-vs-update-mysql). – eggyal Aug 24 '12 at 19:14
  • That is a similar question, but I am asking more along the lines as to whether that is allowable. I'm not too worried about performance in this case (I know, I know) because the application is contained and restricted in use. But is there an easy alternative? The only way to do this with an `update` is using `UPDATE` with a case conditional which is somewhat unsightly. – teynon Aug 24 '12 at 19:18
  • It works, and is quite concise. See my answer below for an alternative. – eggyal Aug 24 '12 at 19:18

1 Answers1

2

Further to my comment above (linking to a question where another poster advises of the performance impact from using INSERT ... ON DUPLICATE KEY UPDATE where the records are known to exist), one could use the multiple-table UPDATE syntax with a table materialised from constants using UNION:

UPDATE my_table JOIN (
    SELECT 'name' AS name, 'mydescription' AS description
  UNION ALL
    SELECT 'name2', 'description2'
) t USING (name) SET my_table.description = t.description
Community
  • 1
  • 1
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • As much as I hate to admit... I'm not quite sure whats going on here... And this is a seemingly complex query, whereas I am trying to keep it simple and maintainable. – teynon Aug 24 '12 at 19:21
  • @Tom: You're just creating a table `t` from a subquery made of two `SELECT , ` statements combined by `UNION`; then joining that table to `my_table` where the `name` columns match and updating the `description` columns as desired. If you don't care about performance and feel this approach is too complicated, then your original approach with `INSERT ... ON DUPLICATE KEY UPDATE` is probably the best way to go. – eggyal Aug 24 '12 at 19:23
  • How would I add multiple columns via this method? – teynon Aug 24 '12 at 19:24
  • @Tom: How do you mean? If you want to update more than just `description` whilst matching on `name`, just add the additional data as new columns in the subquery (e.g. `SELECT ... , 'newdata' AS column_name`) and add a further statement to the `SET` clause (e.g. `SET ... , my_table.column_name = t.column_name`). – eggyal Aug 24 '12 at 19:27
  • I mean if I want to edit two rows of data. Something like `UNION ALL (SELECT 'name2', 'descrip2') (SELECT 'name3', 'descrip3')`? I don't know how I would update those additional rows. I might not be understanding this query still. – teynon Aug 24 '12 at 19:30
  • 1
    You would add further UNIONs. Being constant, they're unexpensive to run, and this way you avoid the risk of re-inserting deleted rows. – LSerni Aug 24 '12 at 19:47
  • Marking as correct because it seems like a reliable and faster way although it is not as simple as I would prefer. (I know programming isn't a beauty contest, but I digress.) – teynon Aug 24 '12 at 20:13
  • @eggyal: Is your solution more performant then using `INSERT ... ON DUPLICATE KEY UPDATE`? I take care of performance and I'm searching for a solution to update multiple rows with multiple values because of the recreation of the indexes after every update. – lszrh Sep 24 '12 at 13:39