CODEWITHSUNDEEP
sqlsql-server
smr5
smr5

Reputation: 2793

SQL update statement is executing but updating with incorrect values

I'm trying to update one of my tables with values from different table. The update statement executes but it doesn't update the column with the correct values.

For example,

This is table1 where I want to update column SID from table2 enter image description here

This is table2 where column SID contains values I need.

enter image description here

What I'm trying accomplish is to update table1 from table2 where uID, NO are equal and table1.NO is not null

Here's how I do it:

update c1 set c1.SID = c2.SID
from table1 c1, table2 c2
    where c1.UID = '09999138181408374834'
    and c1.NO = c2.NO
    and c1.NO is not null

It updates 5 rows, but this is the output:

enter image description here

Those updated SIDs in table1 are not the same as SIDs in table2

Any suggestion on why it's happening and how to resolve it?

Upvotes: 1

Views: 1325

Answers (1)

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

Start the debugging process by verifying that this query returns the correct tuples for the update:

select
     c1.NO as NO_target
    ,c2.NO as NO_source
    ,c1.SID as SID_old
    ,c2.SID as SID_new
from table1 c1
join table2 c2
   on c1.NO = c2.NO
  and c1.UID = c2.UID   -- I believe this line is needed to prevent a partial Cartesian product
where c1.UID = '09999138181408374834'
  and c1.NO is not null

Once the query above is returning the correct tuples for the update, the process of correcting the update is simple.

Upvotes: 2

Related Questions