sql-update

Return pre-UPDATE column values using SQL only

by

Problem The manual explains: The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table’s columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table’s columns are used. The syntax of the RETURNING list is … Read more

PHP MYSQL UPDATE if Exist or INSERT if not?

by

I believe you are looking for the following syntax: INSERT INTO <table> (field1, field2, field3, …) VALUES (‘value1’, ‘value2′,’value3′, …) ON DUPLICATE KEY UPDATE field1=’value1′, field2=’value2′, field3=’value3’, … Note: With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row … Read more

How do I do large non-blocking updates in PostgreSQL?

by

Column / Row … I don’t need the transactional integrity to be maintained across the entire operation, because I know that the column I’m changing is not going to be written to or read during the update. Any UPDATE in PostgreSQL’s MVCC model writes a new version of the whole row. If concurrent transactions change … Read more

Bulk/batch update/upsert in PostgreSQL

by

Bulk insert You can modify the bulk insert of three columns by @Ketema: INSERT INTO “table” (col1, col2, col3) VALUES (11, 12, 13) , (21, 22, 23) , (31, 32, 33); It becomes: INSERT INTO “table” (col1, col2, col3) VALUES (unnest(array[11,21,31]), unnest(array[12,22,32]), unnest(array[13,23,33])) Replacing the values with placeholders: INSERT INTO “table” (col1, col2, col3) VALUES … Read more

Get count of records affected by INSERT or UPDATE in PostgreSQL

by

I know this question is oooolllllld and my solution is arguably overly complex, but that’s my favorite kind of solution! Anyway, I had to do the same thing and got it working like this: — Get count from INSERT WITH rows AS ( INSERT INTO distributors (did, dname) VALUES (DEFAULT, ‘XYZ Widgets’), (DEFAULT, ‘ABC Widgets’) … Read more

mysql update multiple columns with same now()

by

Found a solution: mysql> UPDATE table SET last_update=now(), last_monitor=last_update WHERE id=1; I found this in MySQL Docs and after a few tests it works: the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from … Read more