CODEWITHSUNDEEP
sqloracle-databasesql-updateoracle-sqldeveloper
Chirag Raj
Chirag Raj

Reputation: 35

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

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.

Upvotes: 0

Views: 55

Answers (3)

Sandesh Herwade
Sandesh Herwade

Reputation: 21

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)

Upvotes: 1

Roger Cornejo
Roger Cornejo

Reputation: 1547

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143023

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)

Upvotes: 1

Related Questions