3

I have a MySQL table that will only have one row. What should my statement be for the first time I insert to this row, and for subsequent updates? I tried an insert where the primary key equals 1, but this doesn't account for the first time around when no row exists yet.

niton
  • 8,771
  • 21
  • 32
  • 52

2 Answers2

9

If your table will only ever have one row, you might consider preloading initial data into the row in your database creation script. Then your code will only ever need to issue an UPDATE statement. Also, you will not need a primary key column because there is only ever one row. You can then issue UPDATE statements without needing a WHERE clause, too.

Greg Hewgill
  • 951,095
  • 183
  • 1,149
  • 1,285
  • Thanks for the good tip. Unfortunately, this is a Wordpress plugin, and the db session is already open and initialized. –  Nov 17 '08 at 03:07
8
INSERT INTO table(col1,col2,col3) VALUES(val1,val2,val3) ON DUPLICATE KEY UPDATE col1 = val1, col2 = val2, col3 = val3;
SoapBox
  • 20,457
  • 3
  • 51
  • 87
  • 1
    Is it 100% safe to overwrite pk/ck/ unique cols like that? I know it works. I have tested it on pk and unique. But is it safe, are there any corner cases when it stops working etc. I asked the same in [this](http://stackoverflow.com/questions/32179878/insert-into-on-duplicate-update-all-values) question. – CoR Aug 25 '15 at 09:22