`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.`
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:I replaced
LEFT JOIN
withINNER JOIN
because the clauseWHERE 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 bothVar
values are NULL.If the
Numero
andMatricola
columns are indexed, the database engine will use the indexes to find the matching rows and update them. IfVar
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 hittingCtrl+L
. Azure Data Studio has an equivalent command.