How to us a Subquery to Update Multiple Column Values in SQL?

Question

My ultimate goal is to be able to update multiple column values from one table to another without having to write each one out.

I found the following on IBM's site the indicated how to do it (Link)

UPDATE items
   SET (stock_num, manu_code, quantity) = 
      ( (SELECT stock_num, manu_code FROM stock 
         WHERE description = 'baseball'), 2)
   WHERE item_num = 1 AND order_num = 1001;

UPDATE table1
   SET (col1, col2, col3) =
      ((SELECT MIN (ship_charge), MAX (ship_charge) FROM orders), '07/01/2007')
   WHERE col4 = 1001;

I took this and attempted to create it on my end, but I keep getting an "Incorrect syntax near '('" error.

UPDATE XX__JeremyTempTable2
    SET (OP__DOCID, SexualPrefCode) =
        (SELECT OP__DOCID, SexualPrefCode FROM FD__CLIENTS 
         WHERE CLIENTKEY = 726148)

Tag your question with the database you are using. Off the top of my head, IBM has Netezza, Informix, DB2/UDB . . . and probably others. — Gordon Linoff
– Gordon Linoff, Commented Aug 11, 2017 at 15:05
We are using a SQL-Server. I am still pretty "new" at coding, could this be the reason why the multiple column update isn't working? It only works on the informix servers? — J Lyne
– J Lyne, Commented Aug 11, 2017 at 15:14

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

For MS Sql server your query will be

UPDATE XX__JeremyTempTable2
    SET OP__DOCID = FD__CLIENTS.OP__DOCID,
        SexualPrefCode = FD__CLIENTS.SexualPrefCode
    FROM FD__CLIENTS 
    WHERE FD__CLIENTS.CLIENTKEY = 726148

With such errors you need check manual

Edit Changed to your target query.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 11, 2017 at 15:16

Vitaliy Smolyakov

4723 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J Lyne Over a year ago

Ok, so it looks like the Link I found on IBM's site is for a different server than I currently have.

Rominus · Accepted Answer · 2017-08-11 15:01:01Z

1

You have to set each variable separately:

UPDATE XX__JeremyTempTable2
    SET OP__DOCID = (SELECT OP__DOCID FROM FD__CLIENTS WHERE CLIENTKEY = 726148) ,
    SexualPrefCode = (SELECT SexualPrefCode FROM FD__CLIENTS WHERE CLIENTKEY = 726148)

answered Aug 11, 2017 at 15:01

Rominus

1,2212 gold badges17 silver badges30 bronze badges

1 Comment

J Lyne Over a year ago

This is what I was thinking as well, however, that link I provided indicated that it is possible, hence the confusion.

William A. Staffen · Accepted Answer · 2023-09-19 21:25:55Z

This is an old question, but I don't feel like it has a great answer so I'm adding one.

To use a subselect in an update statement in MS sql server, you have to do a subselect per column as noted by Rominus, which is not terribly satisfying - especially if the subselect is complicated and could easily produce all of the values needing updated.

You cannot mangle Vitaliy Smolyakov's answer in any way that would produce this result; replacing FROM FD__CLIENTS with (select this, that from thing) as FD__CLIENTS does not work, and I tried this a frustrating number of times using his answer.

What you can do instead, is use a Merge statement to update the table.

Before:

    UPDATE items
       SET (stock_num, manu_code, quantity) = 
          ( (SELECT stock_num, manu_code FROM stock 
             WHERE description = 'baseball'), 2)
       WHERE item_num = 1 AND order_num = 1001;

After:

    MERGE items
    USING(SELECT item_num, stock_num, manu_code FROM stock 
         WHERE description = 'baseball') AS BB
    ON items.item_num=BB.item_num
    WHEN MATCHED THEN
    UPDATE SET 
    items.stock_num=BB.stock_num,
    items.manu_code=BB.manu_code,
    items.quantity=2
    WHERE item_num = 1 AND order_num = 1001;

Collectives™ on Stack Overflow

How to us a Subquery to Update Multiple Column Values in SQL?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related