Update Statement using Join Condition

Question

Below is an example. TABLE 1 is manually created where the first three columns are loaded here from an external file. Fourth column(SHOWROOM_ID) will be taken from TABLE2 and the rest of the columns in TABLE 1 will be updated based on criteria.

TABLE 1

NAME     |OLD_CPR_NO |OLD_COS_NO |SHOWROOM_ID|NM_CPR_COS_MAT|NM_CPR_MAT|COS_CPR_MAT|
------------------------------------------------------------------------------------
FORD     | 45        | 487       |           |              |          |    
TOYOTA   | 78        | 562       |           |              |          |
BENZ     | 55        | 789       |           |              |          |
JEEP     | 66        | 124       |           |              |          |
HONDA    | 34        | 142       |           |              |          |
KIA      | 12        | 962       |           |              |          |
GM       | 89        | 7787      |           |              |          |
CHRYSLER | 45        | 236       |           |              |          |
AUDI     | 67        | 4789      |           |              |          |

TABLE 2

PK|NAME     |OLD_CPR_NO |OLD_COS_NO |SHOWROOM_ID
---------------------------------------------
1  |FORD     | 45        | 487       |   1    
2  |TOYOTA   | 78        | 562       |   2   
3  |CIAT     | 55        | 789       |   3     
4  |JEEP     | 66        | 124       |   5    
5  |HONDA    | 34        | 456       |   6   
6  |MUSTANG  | 12        | 962       |   7   
7  |GM       | 89        | 56        |   8   
8  |CHRYSLER | 45        | 236       |   9   
9  |AUDI     | 67        | 4789      |   10

STEP 1: Update NM_CPR_COS_MAT column from table 1. This is an indicator field where NAME,OLD_CPR_NO,OLD_COS_NO matches from TABLE 1 and TABLE 2 then assign indicator 'Y'

I was able to attain the results based on my below query:

UPDATE TABLE_1 TAB1
SET NM_CPR_COS_MAT = (SELECT 'Y'
FROM 
TABLE_2 TAB2
WHERE
TRIM(TAB1.NAME) = TRIM(TAB2.NAME)
AND TRIM(TAB1.OLD_CPR_NO) = TRIM(TAB2.OLD_CPR_NO)
AND TRIM(TAB1.OLD_COS_NO) = TRIM(TAB2.OLD_COS_NO)
;
COMMIT;

UPDATE TABLE_1 TAB1
SET SHOWROOM_ID= (SELECT TAB2.SHOWROOM_ID
FROM 
TABLE_2 TAB2
WHERE
TRIM(TAB1.NAME) = TRIM(TAB2.NAME)
AND TRIM(TAB1.OLD_CPR_NO) = TRIM(TAB2.OLD_CPR_NO)
AND TRIM(TAB1.OLD_COS_NO) = TRIM(TAB2.OLD_COS_NO)
AND TRIM(TAB1.NM_CPR_COS_MAT) = 'Y'
;
COMMIT;

RESULT:

TABLE 1

NAME     |OLD_CPR_NO |OLD_COS_NO |SHOWROOM_ID|NM_CPR_COS_MAT|NM_CPR_MAT|COS_CPR_MAT|
------------------------------------------------------------------------------------
FORD     | 45        | 487       |      1    |       Y      |          |    
TOYOTA   | 78        | 562       |      2    |       Y      |          |
BENZ     | 55        | 789       |           |              |          |
JEEP     | 66        | 124       |      5    |       Y      |          |
HONDA    | 34        | 142       |           |              |          |
KIA      | 12        | 962       |           |              |          |
GM       | 89        | 7787      |           |              |          |
CHRYSLER | 45        | 236       |     9     |       Y      |          |
AUDI     | 67        | 4789      |     10    |      Y       |          |

But I am getting errors if I tried to use the join statements.

UPDATE TABLE_1 TAB1
SET NM_CPR_COS_MAT = 'Y'
FROM 
TABLE_2 TAB2 JOIN
TABLE_1 TAB1 ON 
TRIM(TAB1.OLD_CPR_NO) = TRIM(TAB2.OLD_CPR_NO)
WHERE
TRIM(TAB1.NAME) = TRIM(TAB2.NAME)
AND TRIM(TAB1.OLD_COS_NO) = TRIM(TAB2.OLD_COS_NO)
;
COMMIT;
ORA-00933: SQL command not properly ended.

From the below resulting table, I have to again UPDATE SHOWROOM_ID column and NM_CPR_MAT

TABLE 1

NAME     |OLD_CPR_NO |OLD_COS_NO |SHOWROOM_ID|NM_CPR_COS_MAT|NM_CPR_MAT|COS_CPR_MAT|
------------------------------------------------------------------------------------
FORD     | 45        | 487       |      1    |       Y      |          |    
TOYOTA   | 78        | 562       |      2    |       Y      |          |
BENZ     | 55        | 789       |           |              |          |
JEEP     | 66        | 124       |      5    |       Y      |          |
HONDA    | 34        | 142       |           |              |          |
KIA      | 12        | 962       |           |              |          |
GM       | 89        | 7787      |           |              |          |
CHRYSLER | 45        | 236       |     9     |       Y      |          |
AUDI     | 67        | 4789      |     10    |      Y       |          |


STEP 2:
UPDATE TABLE_1 TAB1
SET NM_CPR_MAT = (SELECT 'Y'
FROM 
TABLE_2 TAB2
WHERE
TRIM(TAB1.NAME) = TRIM(TAB2.NAME)
AND TRIM(TAB1.OLD_CPR_NO) = TRIM(TAB2.OLD_CPR_NO)
AND TRIM(NM_CPR_COS_MAT) IS NULL
;
COMMIT;

UPDATE TABLE_1 TAB1
SET SHOWROOM_ID= (SELECT TAB2.SHOWROOM_ID
FROM 
TABLE_2 TAB2
WHERE
WHERE
TRIM(TAB1.NAME) = TRIM(TAB2.NAME)
AND TRIM(TAB1.OLD_CPR_NO) = TRIM(TAB2.OLD_CPR_NO)
AND TRIM(NM_CPR_COS_MAT) IS NULL
AND TRIM(NM_CPR_MAT) = 'Y'
;
COMMIT;

I AM GETTING THE BELOW RESULTS.I AM GETTING THE CORRECT 'Y' IN NM_CPR_MAT COLUMNS AND ALSO THE CORRECT NUMBERS IN SHOWROOM_ID FOR THE NEW UPDATE STATEMENT BUT THE NUMBERS THAT WAS UPDATED IN THE UPDATED STATEMENT WERE GONE.

TABLE 1

NAME     |OLD_CPR_NO |OLD_COS_NO |SHOWROOM_ID|NM_CPR_COS_MAT|NM_CPR_MAT|COS_CPR_MAT|
------------------------------------------------------------------------------------
FORD     | 45        | 487       |           |       Y      |          |    
TOYOTA   | 78        | 562       |           |       Y      |          |
BENZ     | 55        | 789       |           |              |          |
JEEP     | 66        | 124       |           |       Y      |          |
HONDA    | 34        | 142       |      6    |              |    Y     |
KIA      | 12        | 962       |           |              |          |
GM       | 89        | 7787      |      8    |              |    Y     |
CHRYSLER | 45        | 236       |           |      Y       |          |
AUDI     | 67        | 4789      |           |      Y       |          |

duplicate of: https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle that is not the the correct syntax for an update using a join. — Cyrille MODIANO, Sep 27 '17 at 13:18
Possible duplicate of [Update statement with inner join on Oracle](https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle) — Boneist, Sep 27 '17 at 13:26

score 0 · Answer 1 · answered Sep 27 '17 at 13:30

0

Incorrect syntax, missing (SELECT before 'Y', and don't forget the matching closing bracket at the end.

UPDATE TABLE_1 TAB1
   SET NM_CPR_COS_MAT = (SELECT 'Y'
FROM
TABLE_2 TAB2 JOIN
TABLE_1 TAB1 ON
TRIM(TAB1.OLD_CPR_NO) = TRIM(TAB2.OLD_CPR_NO)
WHERE
TRIM(TAB1.NAME) = TRIM(TAB2.NAME)
AND TRIM(TAB1.OLD_COS_NO) = TRIM(TAB2.OLD_COS_NO));

answered Sep 27 '17 at 13:30

Sabe

1
2

is that the only way to do it? My boss has been arguing me to obtain the results the below way without using select as you mentioned in the above query. UPDATE TABLE_1 TAB1 SET NM_CPR_COS_MAT = 'Y' FROM TABLE_2 TAB2 JOIN TABLE_1 TAB1 ON TRIM(TAB1.OLD_CPR_NO) = TRIM(TAB2.OLD_CPR_NO) WHERE TRIM(TAB1.NAME) = TRIM(TAB2.NAME) AND TRIM(TAB1.OLD_COS_NO) = TRIM(TAB2.OLD_COS_NO) ; COMMIT; – NEWBEE Sep 27 '17 at 14:02
I have to update the SHOWROOM_ID column but there are issues I am encountering. I am updating the question for your reference. Thanks for all your help. – NEWBEE Sep 27 '17 at 14:23
@NEWBEE you can't use that alternative syntax (`update set = , ... from join ...`) because it's not valid Oracle syntax. You could use a MERGE statement instead, as per the proposed duplicate answers in the comments above.

– Boneist Sep 27 '17 at 14:46
@Boneist Thank you. I looked into the other answers and I am getting the results – NEWBEE Sep 27 '17 at 15:48
@boniest Would you please provide your feedback on the second step of my question? – NEWBEE Sep 27 '17 at 15:49
Would someone please provide your feedback on the step 2 of my question. Updating on the same column again on the different row records is overriding the previous update statements. – NEWBEE Sep 29 '17 at 12:28

Update Statement using Join Condition

1 Answers1