Mellad Qarizada Mellad Qarizada - 11 days ago 6
SQL Question

SQL JOIN with UPDATE

i have two tables TBL_TEST1 and TBL_TEST2 with columns DISTRICT and NAME in both tables, there are data in both tables in column DISTRICT which are same i want to update TBL_TEST2 all NAME column values to 'Done' where in TBL_TEST2 table in its DISTRICT value '1' found in both tables.

below are my tables with query

SELECT * FROM TBL_TEST1

DISTRICT NAME
1 Rashid
2 Ramish
1 Ali
1 Karim


And

SELECT * FROM TBL_TEST2

DISTRICT NAME
1 DDD
2 DDDY
3 DDDD7
1 DD1
1 DDD4


When i try

UPDATE TBL_TEST2 SET TBL_TEST2.NAME = 'DONE' FROM TBL_TEST2 INNER JOIN TBL_TEST1 ON TBL_TEST2.DISTRICT = TBL_TEST1.DISTRICT WHERE TBL_TEST2.DISTRICT = '1'


i receive error msg below

ORA-00933: SQL command not properly ended


i tested codes which are in other posts, i am asking that however my code is correct why i am still receiving that error msg

Answer

Your join is wrong (You are using FROM in UPDATE) This syntax for JOIN is not for ORACLE

UPDATE TBL_TEST2 
SET TBL_TEST2.NAME = 'DONE'  
FROM TBL_TEST2 
INNER JOIN TBL_TEST1 ON TBL_TEST2.DISTRICT = TBL_TEST1.DISTRICT 
WHERE TBL_TEST2.DISTRICT = '1';

In ORACLE A simple way for update joined table is based on the use the the joined select as a table

  UPDATE ( 
  SELECT TBL_TEST2.NAME AS OLD_VALUE
  FROM TBL_TEST2 
  INNER JOIN TBL_TEST1 ON TBL_TEST2.DISTRICT = TBL_TEST1.DISTRICT 
  WHERE TBL_TEST2.DISTRICT = '1' ) T
  SET T.OLD_VALUE = 'DONE'  ;
Comments