SQL Server SQL Cursor not working with where not equal

ryoqjall 于 2023-03-17 发布在其他

关注(0)|答案(1)|浏览(169)

`Hi everybody, My target is to update a field of a table (TABLE1) which has different values from its master table (TABLE2) with a SQL Procedure. The select is working (giving about 20 results) but the cursor is not.

DECLARE 
@NUMBER INT,
@MATRICOLA INT,
@VAR VARCHAR(MAX)
@ID INT;

DECLARE CURSOR CURSOR FOR
SELECT  
TABLE1.ID
,TABLE2.[VAR]
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.NUMERO = TABLE2.NUMERO AND TABLE1.MATRICOLA = TABLE2.MATRICOLA
WHERE TABLE1.[VAR] <> TABLE2.[VAR]

OPEN CURSOR
FETCH NEXT FROM CURSOR
INTO @ID, @VAR
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE TABLE1
SET [VAR] = @VAR
WHERE ID = @ID;

FETCH NEXT FROM CURSOR
INTO @ID, @VAR
END

CLOSE CURSOR;
DEALLOCATE CURSOR;

After many tries, I think that the problem is in the part WHERE TABLE1.[VAR] <> TABLE2.[VAR] but i don't know how to substitute this with something else. Thank you for your help.`

sql-server

来源：https://stackoverflow.com/questions/75730312/sql-cursor-not-working-with-where-not-equal

1条答案

按热度按时间

a14dhokn1#

Don't use cursors in the first place. SQL is a set-based language and cursors aren't just cumbersome, they're the slowest possible way of doing anything, as they force the query engine to work by Row-By-Agonizing-Row (that's an actual trade term), instead of using indexes, hashing and parallelism to match and filter rows in memory. Cursors are almost always the wrong solution to whatever problem you may have.

You can write an UPDATE ... FROM... query to update a table using the results of another query, eg:

UPDATE Table1
SET [VAR] = Table2.VAR
FROM Table1
    INNER JOIN Table1 ON 
         TABLE1.NUMERO = TABLE2.NUMERO 
         AND TABLE1.MATRICOLA = TABLE2.MATRICOLA
WHERE TABLE1.[VAR] <> TABLE2.[VAR]

I replaced LEFT JOIN with INNER JOIN because the clause WHERE TABLE1.[VAR] <> TABLE2.[VAR] eliminates NULL values. A NULL in SQL means the value is unknown. You can't compare one Unknown value with another for equality, as the only logical results is Unknown too. TABLE1.[VAR] <> TABLE2.[VAR] will be false even if both Var values are NULL.

If the Numero and Matricola columns are indexed, the database engine will use the indexes to find the matching rows and update them. If Var is also indexed it will use the index to find the rows that don't match. The actual type of matching will depend on the data statistics, eg using Hash matching or Nested loops.

You can inspect the execution plan of the query by clicking on Display Estimated Execution Plan in Management Studio, or hitting Ctrl+L . Azure Data Studio has an equivalent command.

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server SQL Cursor not working with where not equal

1条答案

相关问题

热门标签

最新问答