Update wrong

Databases
1

Hi there, hope in your help.

I tried this select join query and the output is right:

mysql> SELECT
	A.CodeA,
	B.CodeB,
	A.CodeLine,
	B.CodeFlightZone,
	B.DescriptionFlightZone
FROM
	tbl_A A
JOIN tbl_B B ON A.CodeA = B.CodeB
WHERE
	A.CodeLine LIKE '%X1X254107%';
+------------+------------+-----------+----------------+-----------------------+
| CodeA      | CodeB      | CodeLine  | CodeFlightZone | DescriptionFlightZone |
+------------+------------+-----------+----------------+-----------------------+
| X1X2302045 | X1X2302045 | X1X254107 | X11M           | CALGARY               |
+------------+------------+-----------+----------------+-----------------------+
1 row in set

Now I need update in table number three -tbl_C- the field CodeFlightZone replaced with value extracted with the first join and I tried this update query join:

UPDATE tbl_C AS u
JOIN (
	SELECT
		A.CodeA,
		B.CodeB,
		A.CodeLine,
		B.CodeFlightZone,
		B.DescriptionFlightZone
	FROM
		tbl_A A
	JOIN tbl_B B ON A.CodeA = B.CodeB
) AS t
SET u.CodeFlightZone = t.CodeFlightZone
WHERE
	u.CodeFlightZone IS NULL;

But the update is not right because I’ve in field CodeFlightZone of table number three -tbl_C- value X14M and not X11M, as in query select join.

mysql> SELECT
	CodeFlightZone
FROM
	tbl_C
WHERE
	CodeLine LIKE '%X1X254107%';
+----------------+
| CodeFlightZone |
+----------------+
| X14M           |
+----------------+
1 row in set

Any help would be appreciated, thank you.

2

:slight_smile:

UPDATE tbl_C AS u
JOIN (
	SELECT
		A.CodeA,
		B.CodeB,
		A.CodeLine,
		B.CodeFlightZone,
		B.DescriptionFlightZone
	FROM
		tbl_A A
	JOIN tbl_B B ON A.CodeA = B.CodeB
) AS t
SET u.CodeFlightZone = t.CodeFlightZone
WHERE
	u.CodeFlightZone IS NULL;
AND t.CodeLine = u.CodeLine