英文:
Oracle SQL update field in one table with another table using 'where' clause and 'exists' statement
问题
我有2个表（TABLE_1、TABLE_2），我需要使用TABLE_2中的'STATUS_LABEL'字段来更新TABLE_1中的'STATUS'字段，条件如下：
1）在两个表中，'STATUS'和'STATUS_LABEL'字段不匹配。
2）记录存在于TABLE_2中。
3）仅更新'TABLE_1'中类别为'SPARKLA'的记录。
到目前为止，我已经尝试了以下代码，但是当我尝试在Oracle Toad中运行该语句时，加载时间非常长，并且没有返回任何错误，所以我不确定是否做错了什么或是否漏掉了什么：
UPDATE TABLE_1
SET TABLE_1.STATUS=(SELECT TABLE_2.STATUS_LABEL
FROM TABLE_2
WHERE TABLE_2.ID = TABLE_1.ID
AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS)
WHERE TABLE_1.CATEGORY = 'SPARKLA'
AND EXISTS (SELECT 1 FROM TABLE_2 WHERE TABLE_2.ID =TABLE_1.ID);
请您查看我的代码，并指导我该如何操作。
谢谢。
英文:
I have 2 tables (TABLE_1, TABLE_2) and I need to update the 'STATUS' field in TABLE_1 with the 'STATUS_LABEL' field in TABLE_2, where:
- The 'STATUS' and 'STATUS_LABEL' fields do not match in both tables.
- The records exist in TABLE_2.
- Only to update records where the category in 'TABLE_1' is 'SPARKLA'.
I have tried this so far however when I try to run the statement in Oracle Toad, it takes ages to load and doesnt return any errors so i'm not sure if I've done something wrong or if I'm missing something:
UPDATE TABLE_1
SET TABLE_1.STATUS=(SELECT TABLE_2.STATUS_LABEL
FROM TABLE_2
WHERE TABLE_2.ID = TABLE_1.ID
AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS)
WHERE TABLE_1.CATEGORY = 'SPARKLA'
AND EXISTS (SELECT 1 FROM TABLE_2 WHERE TABLE_2.ID =TABLE_1.ID);
Please could you kindly look at my code and guide me what to do.
Thank you.
答案1
得分: 1
更新语句的存在子句应与用于更新的选择语句相同，以避免在选择找不到内容时更新为null。因此，我认为应该如下所示：
UPDATE TABLE_1
SET TABLE_1.STATUS=(SELECT TABLE_2.STATUS_LABEL
FROM TABLE_2
WHERE TABLE_2.ID = TABLE_1.ID
AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS)
WHERE TABLE_1.CATEGORY = 'SPARKLA'
AND EXISTS (SELECT 1
FROM TABLE_2
WHERE TABLE_2.ID = TABLE_1.ID
AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS);
英文:
The where clause for the exists should be the same as for the select used for update so as to avoid updating to null in case that select finds nothing. Thus< I think it should be like below:
UPDATE TABLE_1
SET TABLE_1.STATUS=(SELECT TABLE_2.STATUS_LABEL
FROM TABLE_2
WHERE TABLE_2.ID = TABLE_1.ID
AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS)
WHERE TABLE_1.CATEGORY = 'SPARKLA'
AND EXISTS (SELECT 1
FROM TABLE_2
WHERE TABLE_2.ID = TABLE_1.ID
AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS);
答案2
得分: 1
我同意@nbk的评论，当你有复杂的比较条件时，合并比更新要直接得多。你可以分两步构建一个合并操作：
- 创建一个选择查询，用于识别要更改的记录和要更新的值。
- 将选择查询嵌入到合并语法中。
因此，一个带有内连接的简单选择查询将创建用于更新的事务文件：
Select Table_1.ID, Table_2.Status_Label
From Table_1 Inner Join Table_2
On Table_1.ID=Table_2.ID
Where Table_1.Category='SPARKLA'
And TABLE_2.STATUS_LABEL <> TABLE_1.STATUS
将其添加到合并操作中：
Merge Into Table_1 Using (
Select Table_1.ID, Table_2.Status_Label
From Table_1 Inner Join Table_2
On Table_1.ID=Table_2.ID
Where Table_1.Category='SPARKLA'
And TABLE_2.STATUS_LABEL <> TABLE_1.STATUS
) U On (Table_1.ID=U.ID)
When Matched Then Update Set Table_1.Status=U.Status_Label
英文:
I agree with @nbk comment that a merge is much more straightforward than an update when you have complex comparative conditions. You construct a merge in two steps:
- Create a Select query that identifies the records you want to change and the value you want to update to.
- Embed the Select into the Merge syntax.
So a simple Select with an inner join will create the transactional file you will use to update:
Select Table_1.ID, Table_2.Status_Label
From Table_1 Inner Join Table_2
On Table_1.ID=Table_2.ID
Where Table_1.Category='SPARKLA'
And TABLE_2.STATUS_LABEL <> TABLE_1.STATUS
Adding that into a Merge gets:
Merge Into Table_1 Using (
Select Table_1.ID, Table_2.Status_Label
From Table_1 Inner Join Table_2
On Table_1.ID=Table_2.ID
Where Table_1.Category='SPARKLA'
And TABLE_2.STATUS_LABEL <> TABLE_1.STATUS
) U On (Table_1.ID=U.ID)
When Matched Then Update Set Table_1.Status=U.Status_Label
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论