#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    2
    Rep Power
    0

    Update statement issue


    Hi

    I am trying to write an update statement which updates the User IDs in one table with the User IDs in another table. However I need to update statement to ignore any duplicates that are in the tables.

    Does anyone know how to do this?

    Thank you


    Jesss
  2. #2
  3. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Feb 2007
    Posts
    1,939
    Rep Power
    3120
    You are leaving something out. Are you updating users from one table to another based on User Id or are you actually changing the Ids? Which table might have duplicates?

    What do you have so far?

    Bigger question, why do you have duplicate information, sounds like you need to get rid of one set and just JOIN in your queries.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,746
    Rep Power
    4288
    which database system is this? you may have some options in the sql syntax available to you

    for example, mysql has INSERT IGNORE ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    2
    Rep Power
    0
    Thank you replying

    I am using an Oracle database. I sorry i got in wrong what we are actually tryign to do is update the User Ids in one table with the user IDs which are stored in a variable.

    The table we need to update has two columns User Id and Permit ID but it has duplicate data, however we for some reason need to update the data first then delete the duplicates. So Far i created an update statement which updates the User IDs however it keeps giving an error message about the duplicates. So far we have:
    UPDATE tblRoomsUsers
    SET UsrId=@NewID
    FROM tblRoomsUsers t1 LEFT JOIN tblRoomsUsers t2 ON t1.UsrId = t2.UsrId
    tblRoomsUsers t1 LEFT JOIN tblRoomsUsers t2 ON t1.permit_id = t1.permit_id
    WHERE UsrID=@OldId
    AND NOT UsrId IN (SELECT UsrID
    FROM tblRoomsUsers WHERE
    DelUsrId=@NewID)

    SET @count=@@rowcount
    IF NOT (@count=0)
    BEGIN
    PRINT ' FAILED'
    SELECT @errorencountered=1
    SELECT @errormessages=@errormessages + 'Error updating Room Users table. '
    END

    Do you know of any way to get this statement to ignore the duplicates in th table?

    Thank you


    Jessica
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,746
    Rep Power
    4288
    moved to oracle forum

    Comments on this post

    • LKBrwn_DBA disagrees : SQL posted by OP is NOT Oracle.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo