Update where NOT IN or <>

[benjatado]

I am having problems with this logic...
I am trying to update a table with new data from another table, but only where the values from the new table are not in the old table.

UPDATE Table SET Table.Row1 = AnotherTable.Row1, Table.Row2 = AnotherTable.Row2
FROM AnotherTable
Where Table.Row1 <> AnotherTable.Row1

And I get an error like:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Table'. Cannot insert duplicate key in object 'Table'.
The statement has been terminated.

But I do not want duplicates! And I have the PK set on both tables Row1...so there should not be duplicate entries anyhow?

How do you do this correctly?

 

[SQLBill]

Your table schema and a sample of data would help us on this.

But, something to think about...let's say your primary key on both tables is INT and increases by 1.

Table 1 Table 2
PK Name PK Name
1 Smith 1 Smith
2 Jones 2 Doe
3 Doe 3 Williams
4 Williams

Now, you can see that Jones is in table 1 but not in table 2. So you want to add Jones to table 2, but if you add the whole row you violate the PK constraints as PK 2 already exists. You would need to copy over JUST the name column, which would make Jones PK 4 in table 2.

-SQLBill


Posting advice: FAQ481-4875

 

[benjatado]

Thanks Bill!

My structure relies on PK as a catalog item number, which should be unique to each row, and it is not a generated PK.
Basically the structure of both tables is...

-OldTable- -NewTable-
ItemNumber ItemNumber
Title Title
UPC UPC

I have loaded the NewTable with data that contains new ItemNumbers that don't exist within the OldTable and Old ItemNumbers that do exist within the OldTable.
I want to be able to just update the OldTable with the NewTable data where these ItemNumbers do not exist within the OldTable.

Is this enough information?

Like you mentioned above, to just copy the name column...but in this case I need both the ItemNumber(PK) and the other columns as well.

Thanks again!! ;-)

 

[amrita418]

I think what you need here is an insert than an update. If the itemnumber does not exist in the old table you cannot update it right?

insert into OldTable(col1, col2, col3) 
select    a.col1, a.col2, a.col3 
from      NewTable a
          left outer join OldTable b
          on (a.itemnumber = b.itemnumber)
where     b.itemnumber is null

Regards,
AA

 

[benjatado]

Amrita-

Yes,... that does sound more logical!
I will give this a try!

Thanks!