There are two sql statements in the text please try using edit to see the first statement only the second one seems to appear and I do not know how to change the text to make both appear.
I am performing a recursive update on a key column on a single table. Only certain keys MOD(ind, 10) = 0 are valid in the recursive key indcopy (enforced by a check clause). Would either of the following SQL statements eliminate the rows where MOD(ind, 10) <> 0 before attempting the inner joins? I favor the first because in theory if the where is applied at the appropriate time neither join would ever be attempted. I believe the second would only attempt the first join and not the second. (Unless the optimizer recognizes the difference and performs the desired results (neither join) for both.) This may be trivial in this case however I have other cases with large number of rows where multiple updates using a table scan and outer joins are required. Whether or not the where clause in the first example is applied at the earliest opportunity is of considerable importance to me. In the table ind rowupdkey is the primary key, ind is a unique key and indcopy (child) is a foreign key to ind (parent).
UPDATE data.ind AS t
SET t.indcopy = f.ind
FROM (deleted AS d INNER JOIN data.ind AS f ON f.rowupdkey = d.rowupdkey)
INNER JOIN data.ind AS t ON t.indcopy = d.ind
WHERE MOD(d.ind, 10) = 0
ORDER BY t.ind;
UPDATE data.ind AS t
SET t.indcopy = f.ind
FROM (deleted AS d
INNER JOIN data.ind AS f
ON MOD(d.ind, 10) = 0 AND f.rowupdkey = d.rowupdkey)
INNER JOIN data.ind AS t ON t.indcopy = d.ind
ORDER BY t.ind;
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.