Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: Update Table with value from another table - help

    Hi looking for some advice.

    Basically i have 2 tables are joined together by common field.

    I want to update table 1 with the value in table 2.

    SELECT a.COLUMN1, b.COLUMN1, a.COLUMN2, b.COLUMN2
    FROM TABLE1 a, TABLE2 b
    WHERE a.COLUMN1 = b.COLUMN1

    So in the above COLUMN1 is the JOIN to both tables.

    Now COLUMN2 on TABLE2 has a value updated. I simply want to update TABLE1.COLUMN2 to the value held in TABLE2.COLUMN2 for the same COLUMN1 id.

    In my head the following logic i would have thought worked but it doesn't and looking for help with the syntax please.

    UDPATE TABLE1 a
    SET a.COLUMN2 = b.COLUMN2
    FROM TABLE1 a, TABLE2 b
    WHERE a.COLUMN1 = b.COLUMN1

    However, the above SQL does not work! Be grateful for some advice.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    No FROM clause in UPDATE statement.
    MySQL :: MySQL 5.6 Reference Manual :: 13.2.11 UPDATE Syntax

    Some DBMS(like Microsof SQL server) may support FROM clause(and join in there).
    But, the syntax might be not conform to SQL Standard.
    Please try the UPDATE statement(which include FROM clause) on here...
    Mimer SQL Developers - Mimer SQL-2003 Validator


    The Exanple 1 might be a solution.
    But, a shortcoming might be that COLUMN2 would be updated to null, if no joined row of TABLE2 b was there.
    Example 1:
    Code:
    UPDATE TABLE1 a
      SET  COLUMN2
         = (SELECT b.COLUMN2
             FROM  TABLE2 b
             WHERE b.COLUMN1 = a.COLUMN1
           )
    ;

    To overcome the shortcoming,
    try Example 2 or Example 3.
    Example 2:
    Code:
    UPDATE TABLE1 a
      SET  COLUMN2
         = (SELECT b.COLUMN2
             FROM  TABLE2 b
             WHERE b.COLUMN1 = a.COLUMN1
           )
     WHERE EXISTS
           (SELECT *
             FROM  TABLE2 b
             WHERE b.COLUMN1 = a.COLUMN1
           )
    ;

    Example 3:
    Code:
    UPDATE TABLE1 a
      SET  COLUMN2
         = COALESCE(
              (SELECT b.COLUMN2
                FROM  TABLE2 b
                WHERE b.COLUMN1 = a.COLUMN1
              )
            , a.COLUMN2
           )
    ;
    Last edited by tonkuma; 09-07-13 at 14:40. Reason: Replace UDPATE by UPDATE.

Posting Permissions

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