Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: problem on update

    HI everybody have a problem with updating data based on a condition on both tables..

    T1 (table 1)

    PUBID PUBCODE
    aa
    bb

    T2 (table 2)
    PUBID PUBCODE
    1 zz
    2 aa
    3 bb

    I have to update T1 with the pubid coming from T2 based on pubcode of both

    UPDATE T1
    SET pubid = COALESCE(t2.pubid, 0)

    from T1
    left outer Join T2
    ON
    T1.PUBCODE=T2.PUBCODE
    END

    the stored procedure runs correctly but doesn't update any record.. don't know what's going wrong

  2. #2
    Join Date
    Apr 2008
    Location
    Warrington, UK
    Posts
    15
    Strange - I think you must have a type somewhere - the following works perfectly for me:


    CREATE TABLE T1(pubid int, pubcode varchar(2))
    CREATE TABLE T2(pubid int, pubcode varchar(2))

    INSERT INTO t1(pubcode)
    SELECT 'aa' UNION
    SELECT 'bb'

    INSERT INTO t2(PUBID, PUBCODE)
    SELECT 1, 'zz' UNION
    SELECT 2, 'aa' UNION
    SELECT 3, 'bb'


    UPDATE T1
    SET pubid = COALESCE(t2.pubid, 0)
    FROM T1
    LEFT OUTER JOIN T2
    ON
    T1.PUBCODE=T2.PUBCODE

    SELECT *
    from t1

    SELECT *
    from t2


    Tony
    http://websql.blogspot.com

Posting Permissions

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