1

I have the following query. 

UPDATE A SET b = (SELECT b FROM B WHERE A.a_id = B.a_id AND B.value = ?)

This might fill A with NULL values if no a_id exists in B where value = ?. but this is okay, because before executing this query, it is certain that A.b contains only NULL values to begin with.

However, I need the number of updated columns to reflect the number of updates performed. So I changed it into this:

UPDATE A SET b = (SELECT b FROM B WHERE A.a_id = B.a_id AND B.value = ?)
WHERE EXISTS (SELECT b FROM B WHERE A.a_id = B.a_id AND B.value = ?)

I don't like this solution, because now I have duplicate code and have to fill the parameter multiple times. This gets even uglier when the where clause gets more complicated.

Is there a way to get rid of this duplicate code?

(BTW I'm on Oracle 10, but I prefer DB independent solutions)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
wvdz
  • 16,251
  • 4
  • 53
  • 90

1 Answers1

1

Updat using an inner join

UPDATE A
INNER JOIN B ON A.a_id = B.a_id
SET A.b = B.b
WHERE B.value = ?

If that isn't allowed with your particular RDBMS, perhaps you could SELECT the old and new values into an aliased table expression and update using that. See Update statement with inner join on Oracle

Community
  • 1
  • 1
johan mårtensson
  • 185
  • 1
  • 8