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)

Answer 1

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.