Updating multiple fields with values from another table

Last day we were working with an Application Engine which was relatively simple. It had a single step!

We had to update 3 fields of tableA with values of similar fields from another table, tableB.
Normally we would write an update SQL statement for this, which would have 3 select sub queries and would look something like this.

UPDATE tableA A
SET
A.field1 = (SELECT B1.field1 FROM tableB B1 WHERE B1.keyfield = 'abcd'),
A.field2 = (SELECT B2.field2 FROM tableB B2 WHERE B2.keyfield = 'abcd'),
A.field3 = (SELECT B3.field3 FROM tableB B3 WHERE B3.keyfield = 'abcd'),
A.filed4 = 'Y'
WHERE
A.keyfield = 'abcd';

But after thinking over it for a while and doing some trial and error and Googling, we came up with something better.
This time we just had a single sub query that worked much more effectively.

UPDATE tableA A
SET
(A.field1, A.field2, A.field3, A.filed4) =
(SELECT B.field1, B.field2, B.field3, 'Y' FROM tableB B
WHERE B.keyfield = 'abcd')
WHERE
A.keyfield = 'abcd';

Hope this will save you from writing a select statement each for every field to be updated.

Give it a try!

Tags: 
Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Read previous post:
PeopleSoft Journal Uploader
PeopleSoft Journal Import Error – User-defined type not defined

Close