Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Location
    Chicago area
    Posts
    8

    Unanswered: UPDATE - Asking for help

    I have an update query that is producing undesireable results:

    I have two tables:

    TableA
    PK_FieldA integer not null (primary key)
    Data_fieldA char(1)

    TableA has four rows on it. Their contents are:

    1 A
    2 B
    3 C
    4 D

    TableB
    PK_FieldB integer not null (primary key)
    Data_fieldB char(1)

    TableB has two rows on it. Its row contents are:

    2 X
    4 Y

    My query looks like this:

    UPDATE TableA tb1
    SET Data_fieldA =
    (SELECT Data_fieldB from TableB tb2
    WHERE tb1.PK_FieldA = tb2.PK_FieldB)

    This query will update the second and fourth rows of TableA with a value of X and Y respectively, but the other two rows on TableA will be set to null.

    What am I doing wrong?

    Is there a way to stop the other rows from receiving the null value?

    Please help!!!

    -jP

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: UPDATE - Asking for help

    This works ...

    UPDATE TableA tb1
    SET Data_fieldA =
    coalesce((SELECT coalesce(Data_fieldB,data_fielda) from TableB tb2
    WHERE tb1.PK_FieldA = tb2.PK_FieldB),Data_fieldA)

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: UPDATE - Asking for help

    For the first row, PK_FieldA (1) does not have any match in table b , so the subquery retunrs a null. for the second row, PK_FieldA (2), has a match in B and hence the subquery retuns a value(x) ...

    Hope this helps...

    Cheers

    Sathyaram

Posting Permissions

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