如何在Update语句中循环? [英] How can I loop inside an Update statement?

问题描述

我想在UPDATE语句中循环.有可能这样做吗?它在"rec"变量中给了我一个错误.如果不能，您可以告诉我我该怎么做.这是我的代码，在这里我尝试执行SELECT以便使用所有变量更新所有行.

I want to loop inside an UPDATE statement. Is it possible to do so? Its is giving me an error in "rec" variable. If not can you show me how I can do it. This is my code where I try to do a SELECT in order to update all the rows with all the variables.

         BEGIN
            UPDATE DATMCCN0
                SET E_NOME = (
                FOR rec IN(SELECT IID FROM DATMCCN0)
                LOOP
                SELECT I_NOME FROM DAT_CCNCONFIG0 INNER 
JOIN DATMCCN0 ON DAT_CCNCONFIG0.I_NOME = DATMCCN0.CAPLIC 
    where DATMCCN0.IID = rec.IID) 
                END LOOP;
            END;

推荐答案

使用MERGE语句:

MERGE INTO DATMCCN0 dst
USING DAT_CCNCONFIG0 src
ON (
      src.IID    = dst.IID
  AND src.I_NOME = dst.CAPLIC
)
WHEN MATCHED THEN
  UPDATE SET E_NOME = src.I_NOME;

或者，根据您的逻辑:

MERGE INTO DATMCCN0 dst
USING (
  SELECT b.IID,
         /* MAX(I_NOME) AS */ I_NOME
  FROM   DAT_CCNCONFIG0 a
         INNER JOIN DATMCCN0 b
         ON a.I_NOME = b.CAPLIC
  -- GROUP BY b.IID
) src
ON ( src.IID = dst.IID)
WHEN MATCHED THEN
  UPDATE SET E_NOME = src.I_NOME;

这篇关于如何在Update语句中循环?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！