我在使用自聯接時遇到了 SQL 更新查詢的問題。我在這里查看了多個頁面，但沒有找到正確的解決方案。
假設我有一張如下表。
| ID | 國家 | 城市 | 時間戳 |
|---|---|---|---|
| 1 | |||
| 1 | 印度 | 浦那 | 222 |
| 1 | 印度 | 德里 | 111 |
我想從max(timestamp)它們不為空（group by id）的行中復制國家和城市。
我正在嘗試下面的查詢，這顯然需要更正。
update a1
set a1.country = a2.country,
a1.city = a2.city
from selfjointable a1
inner join selfjointable a2 on (a1.id = a2.id)
where a1.country is null
and a2.country is not null
and a2.timestamp = (select max(a3.timestamp)
from selfjointable a3
where (a3.id = a1.id)
group by a3.id);
請注意：這只是我的問題的簡化版本，有很多行和列，我只想為所有這些行撰寫通用更新查詢
uj5u.com熱心網友回復：
怎么樣merge？
SQL> select * from test order by id, timestamp desc nulls last;
ID COUNTRY CITY TIMESTAMP
---------- ------- ------ ----------
1 India Pune 222 --> MAX timestamp for ID = 1; these values ...
1 India Delhi 111
1 --> ... should be copied here
2 Croatia Zagreb 333
SQL> merge into test a
2 using (select id, country, city, timestamp,
3 row_number() over (partition by id order by timestamp desc nulls last) rn
4 from test
5 ) x
6 on (x.id = a.id and x.rn = 1)
7 when matched then update set
8 a.country = x.country,
9 a.city = x.city,
10 a.timestamp = x.timestamp
11 where a.country is null;
1 row merged.
SQL> select * from test order by id, timestamp desc nulls last;
ID COUNTRY CITY TIMESTAMP
---------- ------- ------ ----------
1 India Pune 222
1 India Pune 222
1 India Delhi 111
2 Croatia Zagreb 333
SQL>
uj5u.com熱心網友回復：
以下查詢有效：
UPDATE selfjointable a1
SET (a1.country, a1.city) = (
SELECT a2.country, a2.city
FROM selfjointable a1
WHERE (a1.id=a2.id)
AND a2.timestamp = (
SELECT max(a3.timestamp)
FROM selfjointable a3
WHERE (a3.id = a2.id) Group by a3.id
)
)
WHERE a1.country is null
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/406930.html
標籤：