SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Update wrong

  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Update wrong

    Hi there, hope in your help.

    I tried this select join query and the output is right:
    Code:
    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:
    Code:
    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.
    Code:
    mysql> SELECT
    	CodeFlightZone
    FROM
    	tbl_C
    WHERE
    	CodeLine LIKE '%X1X254107%';
    +----------------+
    | CodeFlightZone |
    +----------------+
    | X14M           |
    +----------------+
    1 row in set
    Any help would be appreciated, thank you.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Code:
    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
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •