本文介绍了是否使用一列从另一个表更新另一个表中的多个列?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
表A如下所示:
表B如下所示:
使用表A,我需要更新单行中的表B,并使用表A中的DATETIME列来获取表B中每列中的值(忽略表B单个记录中显示的状态)。因此,从表A中的三条记录中,我只需要有一条记录,并将与相应状态匹配的日期放入其中,如果找不到状态,则将表B中的日期放入:‘2999-12-31 23:59:5’ when status = 'CLAIMED' put DATETIME in CLAIMED_DATETIME when status = 'BOUGHT' put DATETIME in BOUGHT_DATETIME when status = 'RETURNED' put DATETIME in RETURNED_DATETIME我尝试了此查询,但它只更新第一列,并且没有更新睡觉。
UPDATE TABLE B a SET CLAIMED_datetime = IFF(STATUS='claimed', DATETIME, '2999-12-31 23:59:5'), BOUGHT_datetime = IFF(STATUS='bought', DATETIME, '2999-12-31 23:59:5'), RETURNED_datetime = IFF(STATUS='returned', DATETIME, '2999-12-31 23:59:5'), FROM TABLE A SRC WHERE a.number_id = SRC.number_id and a.country= SRC.country 我需要表格B如下所示:有人知道查询出了什么问题或如何执行此操作吗?
推荐答案只更新了第一个行的原因是,一旦我们匹配了该行,它就不会再次更新,即使联接导致返回3行。第一行的状态为"声明",并且已更新,那么两个匹配行的睡觉将被忽略。
正如Patrick提到的,我们可以尝试使用Pivot函数将表A中的3行减少到1行,并在其自己的列上显示Status。
下面的查询假设每个number_id和国家/地区只有一个日期时间值:
create or replace table a ( number_id int, country varchar, status varchar, datetime date ); insert into a values (121144, 'USA', 'CLAIMED', '2021-10-10'), (121144, 'USA', 'BOUGHT', '2021-10-11'), (121144, 'USA', 'RETURNED', '2021-10-12'), (121144, 'AU', 'CLAIMED', '2021-09-10'), (121144, 'AU', 'BOUGHT', '2021-09-11'); create or replace table b ( number_id int, country varchar, status varchar, claimed_date date, bought_date date, returned_date date ); insert into b values (121144, 'USA', 'RETURNED', '2999-12-31', '2999-12-31', '2999-12-31'), (121144, 'AU', 'BOUGHT', '2999-12-31', '2999-12-31', '2999-12-31'); MERGE INTO B USING ( SELECT * FROM A PIVOT(MIN(DATETIME) FOR STATUS IN ('CLAIMED', 'BOUGHT', 'RETURNED')) AS P (NUMBER_ID, COUNTRY, CLAIMED, BOUGHT, RETURNED) ) A ON (A.NUMBER_ID = B.NUMBER_ID and A.COUNTRY = B.COUNTRY) WHEN MATCHED THEN UPDATE SET B.CLAIMED_DATE = IFF(A.CLAIMED IS NOT NULL, A.CLAIMED, '2999-12-31 23:59:5'), B.BOUGHT_DATE = IFF(A.BOUGHT IS NOT NULL, A.BOUGHT, '2999-12-31 23:59:5'), B.RETURNED_DATE = IFF(A.RETURNED IS NOT NULL, A.RETURNED, '2999-12-31 23:59:5');合并前:
+-----------+---------+----------+--------------+-------------+---------------+ | NUMBER_ID | COUNTRY | STATUS | CLAIMED_DATE | BOUGHT_DATE | RETURNED_DATE | |-----------+---------+----------+--------------+-------------+---------------| | 121144 | USA | RETURNED | 2999-12-31 | 2999-12-31 | 2999-12-31 | | 121144 | AU | BOUGHT | 2999-12-31 | 2999-12-31 | 2999-12-31 | +-----------+---------+----------+--------------+-------------+---------------+合并后:
+-----------+---------+----------+--------------+-------------+---------------+ | NUMBER_ID | COUNTRY | STATUS | CLAIMED_DATE | BOUGHT_DATE | RETURNED_DATE | |-----------+---------+----------+--------------+-------------+---------------| | 121144 | USA | RETURNED | 2021-10-10 | 2021-10-11 | 2021-10-12 | | 121144 | AU | BOUGHT | 2021-09-10 | 2021-09-11 | 2999-12-31 | +-----------+---------+----------+--------------+-------------+---------------+如果状态是固定的,即只有Claimed、Buy和Return值,或许您可以重新设计表以将它们放入列中?
更多推荐
是否使用一列从另一个表更新另一个表中的多个列?
发布评论