Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Unanswered: Update query Issue

    Hi,

    I am trying to run an update query against a limited number of records in a table where a corresponding record exists in another table.


    update agency_cd_ctrl a
    set a.user_id = (select b.user_id
    from seller_imp b
    where a.user_id = b.alias_user_id)


    The problem i am having is that the code is updating all records in the update table (sets user_id to null where there is no match) - i was hoping just to update the records where there was a match (about 60 records out of 3000) and update to the value from the 2nd table.

    Can anyone point me in the right direction please?

    Thanks
    James

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is the ANSI SQL forum

    first thing you have to do is tell us which database system you're using

    UPDATE syntax varies too much from one to the next

    once we know which database you're using, we'll move this thread to the appropriate forum and you can get the specific help you need

    i'm going to guess SQL Server 2000
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As r937 pointed out, it would help to know which SQL data engine you are using. The UPDATE statement has a lot of vendor/dialect specific add-on features that allow for more concise coding than the ANSI standard permits.

    To use the pure ANSI SQL to get what you've described:
    Code:
    UPDATE agency_cd_ctrl
       SET user_id = (SELECT b.user_id
          FROM seller_imp AS b
          WHERE  b.alias_user_id = agency_cd_ctrl.user_id)
       WHERE EXISTS (SELECT *
          FROM seller_imp AS c
          WHERE  c.alias_user_id = agency_cd_ctrl.user_id)
    -PatP

  4. #4
    Join Date
    Apr 2008
    Posts
    4
    Hi,

    Thanks for the response - i'm trying to run the query against an Oracle 9i database.

    The code below did the trick - thanks for your help...

    James


    update agency_cd_ctrl
    set user_id = (select b.user_id
    from seller_imp b
    where b.alias_user_id = agency_cd_ctrl.user_id)
    where exists (select *
    from seller_imp c
    where c.alias_user_id = agency_cd_ctrl.user_id)

Posting Permissions

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