Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Help With Update Query

Discussion in 'Information Technology' started by Smartin, Nov 10, 2005.

Tags:
  1. Smartin

    Smartin
    Expand Collapse
    Guest

    Firstly, please excuse the multipost. I have been unable to get
    solutions in comp.databases.ms-access.

    Can anyone help me get this update query right?

    The SELECT subquery does select the rows I want to update when taken on
    its own.

    However when I add the UPDATE piece it finds no rows to update. What am
    I missing here...

    ===== begin SQL
    UPDATE Claims SET Payment = 'Not Required'
    WHERE Payment IN
    (
    SELECT Payment From Claims Left Join People
    ON People.ID = Claims.PeopleID
    WHERE People.Foo = People.Bar
    AND Canceled = False
    AND IsNull (Payment)
    );
    ===== end SQL

    I have been offered using aliases and adding parens in the subquery
    WHERE constraints, but neither affects the result.

    Thanks for all help!

    --
    Smartin
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 09 Nov 2005 19:27:29 -0500, Smartin <smartin108@yahoo.com>
    wrote:

    >Firstly, please excuse the multipost. I have been unable to get
    >solutions in comp.databases.ms-access.
    >
    >Can anyone help me get this update query right?
    >
    >The SELECT subquery does select the rows I want to update when taken on
    >its own.
    >
    >However when I add the UPDATE piece it finds no rows to update. What am
    >I missing here...
    >
    >===== begin SQL
    >UPDATE Claims SET Payment = 'Not Required'
    >WHERE Payment IN
    >(
    >SELECT Payment From Claims Left Join People
    >ON People.ID = Claims.PeopleID
    >WHERE People.Foo = People.Bar
    >AND Canceled = False
    >AND IsNull (Payment)
    >);
    >===== end SQL


    Well, as written, the Subquery will select only NULL values of
    Payment, since you're explicitly using IsNull(Payment); you'll then
    set the value of Payment in Claims for those values where Payment IN
    (NULL). I'm guessing that this will return no records. I'd also expect
    the Subquery to return the value of PeopleID rather than the value of
    Payment - if Payment is a number, wouldn't this delete all records
    with a null Payment, whoever they're from?

    Could you explain in words which records should be updated? Which
    fields are in Payment and which in Claims? Does it help to explicitly
    identify the field Payment in the subquery?


    John W. Vinson[MVP]
     
  4. Smartin

    Smartin
    Expand Collapse
    Guest

    John Vinson wrote:

    > Well, as written, the Subquery will select only NULL values of
    > Payment, since you're explicitly using IsNull(Payment); you'll then
    > set the value of Payment in Claims for those values where Payment IN
    > (NULL). I'm guessing that this will return no records. I'd also expect
    > the Subquery to return the value of PeopleID rather than the value of
    > Payment - if Payment is a number, wouldn't this delete all records
    > with a null Payment, whoever they're from?


    I'm not following you here. The subquery alone returns Payment of the
    expected records (Payment is a string representing status, not a
    number). When I wrap the Update around the subquery suddenly nothing is
    selected.

    Why do you say PeopleID would be returned by the subquery?

    > Could you explain in words which records should be updated? Which
    > fields are in Payment and which in Claims? Does it help to explicitly
    > identify the field Payment in the subquery?
    >
    > John W. Vinson[MVP]


    I am trying to update Claims where Payment is Null AND the parent record
    has People.Foo = People.Bar. I've revised the query to show all table
    references explicitly (it doesn't work this way either):

    ===== begin SQL
    UPDATE Claims SET Claims.Payment = 'Not Required'
    WHERE Claims.Payment IN
    (
    SELECT Claims.Payment From Claims Left Join People
    ON People.ID = Claims.PeopleID
    WHERE People.Foo = People.Bar
    AND People.Canceled = False
    AND IsNull (Claims.Payment)
    );
    ===== end SQL

    FWIW I also tried moving 'AND IsNull (Claims.Payment)' to the Update's
    WHERE clause with no effect.

    Thanks for your assistance!
    --
    Smartin
     

Share This Page