How can I update some rows using an inner join in SQL in a way which will work on both mysql and postgres?

Question

I have this SQL which updates rows on MySQL, but I need to rewrite it so that it will work on PostgreSQL as well. I'm aware that I can make it work for PostgreSQL using a different syntax, but I need one statement which will work for both systems.

        UPDATE {coursework_feedbacks} AS f
    INNER JOIN {coursework_submissions} AS s
            ON f.submissionid = s.id
    INNER JOIN {coursework} c
            ON s.courseworkid = c.id
           SET f.stage_identifier = 'assessor_1'
         WHERE f.ismoderation = 0
           AND f.isfinalgrade = 1
           AND c.numberofmarkers = 1

score 3 · Answer 1 · edited May 23 '17 at 10:33

As this particular case can be phrased with a correlated subquery, you can just write:

    UPDATE {coursework_feedbacks} AS f
       SET f.stage_identifier = 'assessor_1'
     WHERE f.ismoderation = 0
       AND f.isfinalgrade = 1
       AND EXISTS (
             SELECT 1 
             FROM {coursework_submissions} AS s
             INNER JOIN {coursework} c ON s.courseworkid = c.id
             WHERE f.submissionid = s.id AND c.numberofmarkers = 1
           );

but in the more general case where that isn't true (say, where you need to update a value based on the result of the join) I don't know if there is a portable, efficient way.

How can I update some rows using an inner join in SQL in a way which will work on both mysql and postgres?

1 Answers1