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

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.

which database system is this? you may have some options in the sql syntax available to you

for example, mysql has INSERT IGNORE ...

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

moved to oracle forum