Correlated Updates
It’s always interesting when I answer questions. This question was how to you perform a correlated UPDATE
statement. My answer was assuming you’re updating the rating_id
foreign key column in the rating
table with the value from an item_rating
column in the item
table where on or another column value in the rating
table match the item_rating
column value in the item
table match, you would write a correlated UPDATE
statement like:
UPDATE item i SET i.rating_id = r.rating_id WHERE EXISTS (SELECT NULL FROM rental r WHERE r.rating = i.item_rating OR r.description = i.item_rating); |
This works in Oracle, MySQL, MariaDB, and MS SQL Server. I thought my work was done but I was wrong. The individual was trying to write a correlated UPDATE statement for PostgreSQL. The statement returned the following error:
ERROR: syntax error at or near "WHERE" LINE 3: WHERE EXISTS ^ |
I did didn’t find an article to point the individual to after quick Google and DuckDuckGo searches. So, I thought I’d provide how you do it in PostgreSQL:
UPDATE item i SET rating_id = r.rating_id FROM rating r WHERE r.rating = i.item_rating OR r.description = i.item_rating; |
In short, PostgreSQL doesn’t do what most expect because the UPDATE
statement supports a FROM
clause. Let’s give them the prize for different dialect. While I hope that I’m not a syntax bigot because I use MySQL more, I think the default syntax should always be supported in SQL dialects. After all, MySQL has a far superior named-notation INSERT
statement alternative to the standard with the assignment method but MySQL also supports the standard syntax.
While I’ve shown you how to do it in PostgreSQL, what do you think? Should PostgreSQL be as responsible as MySQL is in maintaining standard SQL approaches?