SQL Update Select Statement Subquery

Question

I am stuck on an SQL Update Statement and was hoping for guidance on where I am going wrong.

This query below yields the following results.

SELECT a.ident, a.iata_code, m.nameAirport, m.codeIataAirport, m.codeIcaoAirport FROM airports a
INNER JOIN mytable m ON m.codeIataAirport = a.iata_code
where m.codeIcaoAirport IS NULL

results:

ident.   iata_code.  nameAirport. codeIataAirport.  codeIcaoAirport
OIAG.    AKW.        Aghajari.    AKW.              NULL
FMNE.    AMB.        Ambilobe.    AMB.              NULL
FTTU.    AMO.        Mao.         AMO.              NULL

I need to update mytable.codeIcaoAirport with the airports.ident column where the codeIcaoAirport field is NULL. In doing so I attempted this query which updated all of the codeIcaoAirports with the last records airports.ident. I have read through the statement a number of times and can't seem to find why it would do this

WITH subquery AS (
SELECT a.ident, m.nameAirport, m .codeIcaoAirport FROM airports a
INNER JOIN mytable m ON m.codeIataAirport = a.iata_code
where m.codeIcaoAirport IS NULL
)
UPDATE mytable
SET codeIcaoAirport = subquery.ident
FROM subquery;

How can I restructure this query to update mytable.codeIcaoAiport with the airports.ident values. The nexus between the tables is on the Inner join or m.codeIataAirport = a.iata_code

Answer 1

If it is SQLite you can use this.

--  before the update
SELECT * FROM mytable;

-- Update "mytable" based on the "airports" table
UPDATE mytable
SET codeIcaoAirport = (SELECT ident FROM airports WHERE airports.iata_code = mytable.codeIataAirport)
WHERE codeIcaoAirport IS NULL;

--  after the update
SELECT * FROM mytable;