Oracle: Trying 'Update' statement using combination of unique Keys. Stuck in the where clause

Question

I'm trying to use an update statement using a combination of unique keys. These unique key are used in the where clause which are dragged from a select query using a sub query. Not sure how to use these two together.

Query looks something like this

1    UPDATE table1
2    SET column1 = .. , column2 = ..
3    WHERE TOOL_NO, TOOL_SERIAL_NO IN
4    (SELECT TOOL_NO, TOOL_SERIAL_NO FROM TABLE2 WHERE condition)

TOOL_NO and TOOL_SERIAL_NO are the unique keys and are dependent on each other. For example Tool_No will have multiple Tool_Serial_No.

The problem is with line number 3. Not sure how to use two fields in the same where clause which depends on the same sub - query,

Any help is appreciated.

Answer 1

You're quite close - just enclose those columns in line #3 into brackets:

UPDATE table1
   SET column1 = .. , column2 = ..
   WHERE (TOOL_NO, TOOL_SERIAL_NO) IN
     (SELECT TOOL_NO, TOOL_SERIAL_NO FROM TABLE2 WHERE condition)

Answer 2

You will also need to correlate the sub query against table2 with the outer where in table1 SQL.

Answer 3

While matching multiple columns in where clause together always enclose it into brackets. So in your case WHERE TOOL_NO, TOOL_SERIAL_NO should be WHERE (TOOL_NO, TOOL_SERIAL_NO)

 UPDATE table1
    SET column1 = .. , column2 = ..
    WHERE (TOOL_NO, TOOL_SERIAL_NO) IN
    (SELECT TOOL_NO, TOOL_SERIAL_NO FROM TABLE2 WHERE condition)