SQL JOIN with UPDATE

Question

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

You tagged your question with `mysql` but the error message suggests that you are in fact using `oracle` which uses a different syntax for update with joins. See this question: https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle — jpw, Nov 21 '16 at 07:25
Another SO question that might help: http://stackoverflow.com/questions/8940471/sql-error-ora-00933-sql-command-not-properly-ended — Conrad Lotz, Nov 21 '16 at 07:31
i test codes which are in other posts, i want to know what is wrong with my code that i am still receiving that error msg — Mellad Qarizada, Nov 21 '16 at 07:32
i tested the code in blow post http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server — Mellad Qarizada, Nov 21 '16 at 07:37
@MelladQarizada That question is for Microsoft SQL Server not Oracle and not applicable here. — jpw, Nov 21 '16 at 07:41

score 6 · Accepted Answer · edited Nov 21 '16 at 08:21

6

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'  ;

edited Nov 21 '16 at 08:21

answered Nov 21 '16 at 07:58

ScaisEdge

131,976
10
91
107

also it is good to mention that in Oracle your tables need to have relation with each other in order to update a join result between them!! – Sobhan Nov 16 '21 at 08:05

SQL JOIN with UPDATE

1 Answers1