UPDATE & SELECT & INNER JOIN combined (SQL92) — How?

Question

I have two tables with the following columns:

Table1
id 
Sort
dou

and

Table2
id_Table1
Sort

and I need to read all values of Table1.Sort where Table1.dou = 1 and write them into Table2.Sort, into those rows where Table2.id_Table1 matches the Table1.id values.

I can do: SELECT Sort FROM Table1 WHERE dou = 1

And I can do UPDATE Table2 SET Sort WHERE id_Table1 = 'uuid'

But how can I combine these in a way that all Table1.Sort values overwrite the Table2.Sort values?

In my version of SQL92 I can use INNER JOIN statements, but I don’t know how close it adheres to the standard. I have no idea this could work with an UPDATE statement.

I'm using FileMaker SQL engine.

Ergest Basha · Accepted Answer · 2023-09-14T18:12:27.323

According to the ANSI SQL-92 standard 13.9 page 391, an UPDATE using JOIN is NOT part of the standard.

You could use a correlated subquery.

Qyery,

UPDATE Table2 
SET Sort = ( SELECT Sort 
             FROM Table1 
             WHERE Table1.id=Table2.id_Table1 
             )

Update join different dbms

SQL Server

UPDATE Table2 
SET  Table2.Sort = Table1.Sort
FROM Table2 t2 
INNER JOIN Table1 t1 ON t1.id=t2.id_Table1

PostgreSQL

UPDATE Table2 t2
SET  t2.Sort = t1.Sort
FROM  Table1 t1
WHERE t1.id=t2.id_Table1

MySQL / MariaDB

UPDATE Table2 t2
INNER JOIN Table1 t1 on t1.id=t2.id_Table1  
SET  t2.Sort = t1.Sort

The following qyery was used by OP and solves the problem

UPDATE Table2 
SET Sort = ( SELECT Sort 
             FROM Table1 
             WHERE Table1.dou = 1 
             AND Table1.id = Table2.id_Table1 ) 
WHERE Table2.dou = 1

UPDATE & SELECT & INNER JOIN combined (SQL92) — How?

1 Answers1