CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
user1597398
user1597398

Reputation: 131

Update query not updating all records

I have an update query to update records in a table, when i do a select on the criteria to be updated, it shows 715 records exist that match the condition but when I run the update query, only 15 of them get updated. Can anyone help me with this. I have loaded the data from the excel sheet into a temp table for the comparison.

    BEGIN TRAN 
    UPDATE table1
    SET    English = CASE 
                  WHEN English <> t.name THEN t.name 
                  else English 
                END

   FROM   #tempdata t 
   LEFT JOIN dbo.Main m 
          ON m.Code= t.[property] 
   LEFT JOIN table1 tbv 
          ON m.filterkey= tbv.filterkey
   where t.name != tbv.English 
   ROLLBACK TRAN

Upvotes: 1

Views: 2468

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Most probably joins are duplicating your data from table1. Here is the test scenario for replication:

SET NOCOUNT ON

DECLARE @table1 TABLE(id INT, col INT)
DECLARE @table2 TABLE(id INT, rel_id int)

INSERT INTO @table1 VALUES(1, NULL),(2, null)
INSERT INTO @table2 VALUES(1, 1),(2, 1),(3,2)


SELECT * FROM @table1 t1
JOIN @table2 t2 ON t2.rel_id = t1.id

SET NOCOUNT OFF

UPDATE t1 SET col = t2.id
FROM @table1 t1
JOIN @table2 t2 ON t2.rel_id = t1.id

This selects 3, but updates only 2 rows.

Upvotes: 4

Related Questions