UPDATE语句:在每个SET之后重新评估子查询 [英] UPDATE statement: re-evaluate subquery after each SET
问题描述
CREATE TABLE test (id NUMBER(3));
INSERT INTO test VALUES (1);
INSERT INTO test VALUES (2);
INSERT INTO test VALUES (2);
INSERT INTO test VALUES (3);
INSERT INTO test VALUES (4);
INSERT INTO test VALUES (4);
INSERT INTO test VALUES (5);
我想使用以下查询使test中的数字唯一(如{1,2,3,4,5,6,7}-即删除双精度):
I want to make the numbers in test unique (like {1,2,3,4,5,6,7} - i.e. remove doubles) using this query:
UPDATE test u SET u.id = (SELECT max(nn.id) FROM test nn) + 1
WHERE 1 < (
SELECT tt.rown
FROM (SELECT rowid, row_number() over ( partition by t.id order by t.id) AS rown FROM test t) tt
WHERE tt.rowid = u.rowid
);
上面的查询将表更新为{1,2,3,4,5,6,6}.它将第二个2正确替换为6，但是第二个4也变为6，应该为7.不应该替换现有的非重复项，而只能替换第二个重复项.
The above query updates the table to {1,2,3,4,5,6,6}. It replaces the second 2 to 6 correctly, but the second 4 becomes 6 also, which should be 7. Existing non-duplicates should not be replaced, but only the second-onward duplicates.
上面的update语句的问题是(SELECT max(nn.id) FROM test nn)被评估一次并被缓存，但是实际上取决于更新的内容.每个SET之后是否可以强制重新评估(SELECT max(nn.id) FROM test nn)?我尝试了类似/*+NOCACHE*/的提示，但没有成功.
The problem with the above update statement is that (SELECT max(nn.id) FROM test nn) is evaluated once and cached, but it actually depends on what is updated. Is it possible to force re-evaluation of (SELECT max(nn.id) FROM test nn) after each SET? I tried some hints like /*+NOCACHE*/ without success.
换句话说，在更新期间，您需要考虑已经更新的字段.
In other words, during update, you need to take into account the fields that already have been updated.
有什么想法吗?
我认为可以使用NON-DETERMINISTIC函数解决此问题，但是我不想创建函数.如果我尝试用函数计算id我得到 ORA-04091:表测试正在变异，触发器/函数可能看不到它.使用PRAGMA AUTONOMOUS_TRANSACTION;可以得到与上述查询相同的结果.
I assume this could be solved with NON-DETERMINISTIC function, but I don't want to make functions. if I try to compute the id with a function i get ORA-04091: table TEST is mutating, trigger/function may not see it. Using PRAGMA AUTONOMOUS_TRANSACTION; gives the same result as the above query.
AudriyM使用CTE解决了MS SQL Server 2008的这一问题(参见注释).据我所知，Oracle中的CTE没有其他选择，但是由于AudriyM的解决方案基于id的预先计算的值，因此我可以在Oracle中使用子查询将其转换.这里是:
AudriyM solved this (cf. comments) for MS SQL Server 2008 using CTEs. There is no alternative in Oracle for CTEs as far as I know, but because AudriyM's solution was based on precalculated values for ids, I though I could translate it in Oracle with subqueries. And here it is:
UPDATE test u SET u.id = ( SELECT newIDs.newID
FROM ( SELECT ranked.rowid,
ranked.m + row_number() over (order by ranked.id, ranked.r) as newID
FROM ( SELECT t.rowid, t.id, row_number() over ( partition by t.id order by t.id) AS r, max(id) over() AS m
FROM test t ) ranked
WHERE ranked.r > 1 ) newIDs
WHERE u.rowid = newIDs.rowid )
WHERE u.rowid IN ( SELECT ranked.rowid
FROM ( SELECT t.rowid, t.id, row_number() over ( partition by t.id order by t.id) AS r, max(id) over() AS m
FROM test t ) ranked
WHERE ranked.r > 1 );
使用查询重新评估无需预计算ID的解决方案
仍未找到，问题仍未得到解决.
Solution without precalculated ids, using query re_evaluation
Still not found and the question remains unanswered.
推荐答案
尝试使用以下语句.您的表格不允许标识ID例如为2的行，因此要链接具有ROW_NUMBER的表格，您可以使用 ROWID
Try to use the following statement. Your table doesn't allow to identify a row for example with ID=2 so to link table with ROW_NUMBER you can use ROWID
UPDATE
Test
SET id = (SELECT RN FROM
(SELECT ROW_NUMBER()
OVER (ORDER BY ID) as RN
FROM Test
) T1
WHERE T1.RowID=Test.RowID
)
这篇关于UPDATE语句:在每个SET之后重新评估子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
