updates in PostgreSQL, MS SQL, MySQL

PostgreSQL: OK, MS SQL: OK (just change the "||" to "+"), MySQL: NOT OK

/* very orthogonal, since only one table allowed to be updated, no need to "set product.name = ", "set name = " will do. in fact if you do: "set product.name = ", postgres will disallow it */

update product set name = product.name || ':' || prod.name
from prod
where prod.seq_id = product.seq_id



**********************************

PostgreSQL: OK (good for re-using existing query, must alias the source table if the source table conflicts with to-be-updated-table name), MS SQL: NOT OK, MySQL: NOT OK

update product set name = product.name || ':' || prod.name
from product x
inner join prod on prod.seq_id = x.seq_id
where x.seq_id = product.seq_id /* don't forget to filter the to-be-updated-table to queried rows */



**********************************

PostgreSQL: NOT OK, MS SQL: NOT OK, MySQL: OK

/* note: even only one possible table can be updated, requires the fully-qualified name for the fields to be set. i.e. set product.name */

update product,prod set product.name = concat(product.name, ':', prod.name)
where prod.seq_id = product.seq_id;



**********************************

PostgreSQL: NOT OK, MS SQL: OK (good for re-using existing query), MySQL: NOT OK

update product set name = product.name + ':' + prod.name
from product /* limitation: cannot alias this, must be same name as the table to-be-updated */
inner join prod on prod.seq_id = product.seq_id

***********************************


PostgreSQL: NOT OK, MS SQL: NOT OK, MySQL: OK


update product join prod on prod.seq_id = product.seq_id

set product.name = concat(product.name, ':', prod.name);


/* nice update syntax, not ambiguous as MS SQL. similar to MS Access update join syntax, though not as good/flexible as PostgreSQL, wherein you can just drop-in your previous query to the update statement */