I need to update more than one record at once. I have ~ 100 records that I have to update and don't want to execute query 100 times.

My query looks like this:

Update Table1

Set Table1.field1 = ( select Table2.field1 from Table2 where Table2.field2 IN ('a', 'b', 'c')

where Table1.field2 IN ( 'a', 'b', 'c')

It obviously failed because subquery returned more than one value and error message stated that I can't use '=' operator in this case.

My question: how could I update the same column from many records in one execution?

Thanks

Try

-- code #1
UPDATE T1
 set field1 = T2.field1
  from Table1 as T1
       inner join Table2 as T2 on T1.field2 = T2.field2
  --where T1.field2 in ( 'a', 'b', 'c');

Merge Table1 as T1
using Table2 as T2 on T1.field2 = T2.field2
When matched then
Update
Set  set field1 = T2.field1;

I need to update more than one record at once. I have ~ 100 records that I have to update and don't want to execute query 100 times.

My query looks like this:

Update Table1

Set Table1.field1 = ( select Table2.field1 from Table2 where Table2.field2 IN ('a', 'b', 'c')

where Table1.field2 IN ( 'a', 'b', 'c')

It obviously failed because subquery returned more than one value and error message stated that I can't use '=' operator in this case.

NO, of course not! First of all, an UPDATE is not a query. The SET clause in an UPDATE statement has two parts, the second is the equal sign. It is assignment, not a predicate. 

Rows are nothing like records; columns are nothing like fields; this is a basic concept in RDBMS.   All operations, including UPDATEs, in SQL  work on sets of rows. A set can have zero or more elements. A set cannot fit into a scalar column. An empty result set will be cast as a NULL; a single row, single column result will be cast as a scalar.

What are you trying to do? Why did you think it would work this way? 

Folks,

Sorry for bringing confusion ... Actually I realized that I failed to simplify my task and presented not accurate statement. Here is my 'actual' statement:

UPDATE TRANSACTIONS

   SET TRANSACTION_DT =

          (SELECT DISTINCT min (it.RACK_COMPLETED_DATE)

             FROM TRANSACTIONS tr

                  JOIN INVENTORY_TRANSACTIONS it

                     ON tr.TRANSACTION_ID = it.TRANSACTION_ID

                  JOIN inventory i ON i.INVENTORYID = it.INVENTORYID

            WHERE     tr.TRANSACTION_TYPE LIKE 'HM'

                  AND i.EXTERNAL_CODE IN ('XYZ120938', 'XYZ122160')

           GROUP BY i.EXTERNAL_CODE)

  FROM inventory i

       JOIN INVENTORY_TRANSACTIONS it ON i.INVENTORYID = it.INVENTORYID

       JOIN transactions tr ON it.TRANSACTION_ID = tr.TRANSACTION_ID

       JOIN transaction_reception tre

          ON tr.transaction_id = tre.transaction_id

WHERE     i.external_code IN ('XYZ120938', 'XYZ122160')

       AND tr.TRANSACTION_TYPE = 'AR'

My goal is to update value of TRANSACTION_DT for more than 1 row.

Could it be re-constructed to do that? I simply don't want to run UPDATE statement 100 times and every time replace the value of 'XYZ...'

Sorry again ...

Thanks

As you have not provided sample DDL and DML, we will not be able to test .\

You may try the below:

UPDATE TRANSACTIONS

   SET TRANSACTION_DT =

          (SELECT min (it.RACK_COMPLETED_DATE)

             FROM TRANSACTIONS tr

                  JOIN INVENTORY_TRANSACTIONS it

                     ON tr.TRANSACTION_ID = it.TRANSACTION_ID

                  JOIN inventory in ON in.INVENTORYID = it.INVENTORYID

            WHERE     tr.TRANSACTION_TYPE LIKE 'HM'

                  AND in.EXTERNAL_CODE = i.external_code --IN ('XYZ120938', 'XYZ122160')

           GROUP BY in.EXTERNAL_CODE)

  FROM inventory i

       JOIN INVENTORY_TRANSACTIONS it ON i.INVENTORYID = it.INVENTORYID

       JOIN transactions tr ON it.TRANSACTION_ID = tr.TRANSACTION_ID

       JOIN transaction_reception tre

          ON tr.transaction_id = tre.transaction_id

WHERE     
--i.external_code IN ('XYZ120938', 'XYZ122160') AND 
tr.TRANSACTION_TYPE = 'AR'

Thanks,

I tried to execute your statement and it should work but it tried to updated ~ 40K rows in my table.

This is not my goal. In my case I want to update only selected rows based on 'XYZ...' values

Could it be changed in order to adopt that request?

Thanks

Here is what I suggest. Start from a select query. Make sure the select query works as expected and only 1 row from your other tables correspond to each row that you will need to update.

Once you get the SELECT statement working correctly, it will be quite easy to turn it into update.