How does update join on work in sql

Question

I know how to use join in sql to combine two tables, here is an example: https://www.w3schools.com/sql/sql_join.asp

Today I read this: https://www.javatpoint.com/sql-update-with-join

I can't understand how update + join works. Can someone explain this to me with enough details?

@Jarlh If I want to use ANSI SQL, what kind of sql query should be used to do the same thing? — Yves, Jul 20 '17 at 12:38

score 0 · Accepted Answer · answered Jul 24 '17 at 01:36

If you don't know what it does then what is the point of asking how to do whatever that is in standard sql? Tell us what DMBS/SQL you are using and we can tell you what its syntax means. After you have read the documentation and googled, if you are still stuck.

That site doesn't mention a DBMS/SQL for that syntax. (Both it and w3schools are poorly written resources which you should not use.) But it seems to give info only on MySQL & Oracle. The first hits from google 'mysql update join' & 'oracle update join' explain the MySQL update join syntax and tell you that Oracle doesn't have it and how to do the same thing in Oracle.

UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';

UPDATE 
(SELECT table1.value as OLD, table2.CODE as NEW
 FROM table1
 INNER JOIN table2
 ON table1.value = table2.DESC
 WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW

How does update join on work in sql

1 Answers1