Is it possible to combine these UPDATE queries?

UPDATE myTable1 SET fld1 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 45;
UPDATE myTable1 SET fld2 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 46;
UPDATE myTable1 SET fld3 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 47;

And also, how is the lock on these two tables?

  • 2
    You are essentially asking two different questions, one about combining update statements and the other about locks. That doesn't work well with this site's Q&A format, so please try to avoid that in the future. Other than that, welcome to the site! – Andriy M Jun 22 '19 at 17:28
  • You're right.Thank you – sam Jun 24 '19 at 8:46

The question about locks was addressed by a_horse_with_no_name in the comments to the question:

an UPDATE will never lock a table - it will only lock the rows that have been updated (in the target table). A select never locks any rows

And as for combining the three update statements into one, yes, it is possible, though not necessarily a good idea. Still, if you insist, here is one way:

  fld1 = CASE myTable2.style WHEN 45 THEN myTable2.value ELSE myTable1.fld1 END,
  fld2 = CASE myTable2.style WHEN 46 THEN myTable2.value ELSE myTable1.fld2 END,
  fld3 = CASE myTable2.style WHEN 47 THEN myTable2.value ELSE myTable1.fld3 END
  myTable1.id = myTable2.id
  AND myTable2.style IN (45, 46, 47)

Basically, you do the join, combine the three filter conditions with an IN predicate and in in each of the three assignments you use a CASE expression to check if the myTable2.style value matches a corresponding value from the filter, so that you can decide whether to assign the myTable2.value to the column (THEN branch) or keep the old value (ELSE branch).

Your three separate statements look clearer/more readable to me, though.

  • I would have used PIVOT within a MERGE statement. – Michael Kutz Jun 22 '19 at 20:52
  • 2
    @MichaelKutz: That's an interesting idea. Of course, this is PostgreSQL 9.3 we are talking about, and so neither PIVOT nor MERGE is available, but I'd be interested to see what you have in mind. I must add, though, that the way I see it, I doubt very much that using PIVOT and MERGE would make the logic any clearer, but as an exercise, this would be interesting indeed. And then there's often the performance aspect to take into account, too, so in that regard it's always good to have options. But again, that solution wouldn't work for the OP's database product. – Andriy M Jun 23 '19 at 1:33
  • A google search pulled up the MERGE syntax for PostrgreSQL, I didn't read the fine-print. "Performance" will probably be RDBMS specific (eg Oracle's MERGE would perform significantly better for this case). As far as "readability" for use of PIVOT, that is definitely a concern. As long as the difference in performance is negligible, I'm not going to argue which method is used (but would probably push for PIVOT to changing rows table2.style in (45,46,47) into columns fld1,fld2,fld3) – Michael Kutz Jun 24 '19 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.