Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    33

    Red face Unanswered: UPDATE sql - INNER JOIN

    In DB2/ZOS/os390 will the inner join works in an update statement
    UPDATE S01.CLNT_T CAR
    INNER JOIN S01.ADDR_T ADR
    ON CAR.ADDR_ID = ADR.ADDR_ID
    SET CAR.ZIP = SUBSTR(ADR.ZIP,1,5)
    WHERE CAR.KEY = 1;

    If yes.. will the above statement work? I have got some sql error working with this.. any help is appreciated.
    bs

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    No, you can't use a join in that way for an update.

    Try this instead:

    UPDATE S01.CLNT_T CAR
    SET CAR.ZIP =
    ( SELECT SUBSTR(ADR.ZIP,1,5)
    FROM S01.ADDR_T ADR
    WHERE ADR.ADDR_ID = CAR.ADDR_ID )
    WHERE CAR.KEY = 1;

  3. #3
    Join Date
    Sep 2003
    Posts
    33
    Thanks a lot umayer.

    If i need to update more than one column , do i need to add another sub query for that variable is it.
    bs

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    There are two alternatives to update more than one row:

    you can use more than one assignment clauses:

    UPDATE table
    SET column1 = ( SELECT col1 FROM .... ) ,
    column2 = ( SELECT col2 FROM .... )
    WHERE ....


    or you can bracket the columns:

    UPDATE table
    SET ( column1 , column2 ) = ( SELECT col1, col2 FROM .... )
    WHERE ...

Posting Permissions

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