請問 我有兩個資料表A,B如下:我想寫一個語法直接把TableB的token更新到TableA的token,
由於資料庫有百萬筆資料,所以如果逐步跑的話,時間會有點久,請問有什麼語法可以直接判斷然後更新過去嗎?
Table A:
username token
user01 0
user02 0
user03 0
Table B:
username token
user01 1
user02 2
user03 3
已邀請的邦友 {{ invite_list.length }}/5
我不知道你的資料庫是什麼
先提供SQL Server版本:
--測試資料
CREATE TABLE TableA
([username] varchar(6), [token] int)
;
INSERT INTO TableA
([username], [token])
VALUES
('user01', 0),
('user02', 0),
('user03', 0),
('user04', 0)
;
CREATE TABLE TableB
([username] varchar(6), [token] int)
;
INSERT INTO TableB
([username], [token])
VALUES
('user01', 1),
('user02', 2),
('user03', 3),
('user04', null)
;
update T
set token = newtoken
from (
select T1.token,T2.token newtoken
from TableA T1
left join TableB T2 on T1.username = T2.username
)T;
--結果
| username | token |
|----------|--------|
| user01 | 1 |
| user02 | 2 |
| user03 | 3 |
| user04 | (null) |
你先看一下有問題再跟我說一下 :-)
你好哦,我使用的postgresql,
目前使用
update customer as tableA
set token = tableB.token
from
(
select * FROM dblink('xxxxx','select username,token from customer') as t(username varchar,token numeric)
)
as tableB where tableA.username = tableB.username
是可行的,請問這樣會有什麼問題嗎?
請問這樣會有什麼問題嗎?
有，而且很大問題
因為你的資料量有百萬筆資料，注意記憶體是否足夠!
DBLINK select 是放在記憶體裡面
假如記憶體不夠請分批更新!
要不就把dblink資料先insert資料到本地DB裡面，再去update
其餘問題是:效率會變慢、假如是外網環境會有延遲被中斷風險
以上確定沒問題，正常使用是可以的
postgresql用以下script
記得大量更新前要備份! 以免有意外
CREATE TABLE TableA
(username varchar(6), token int)
;
INSERT INTO TableA
(username, token)
VALUES
('user01', 0),
('user02', 0),
('user03', 0),
('user04', 0)
;
CREATE TABLE TableB
(username varchar(6), token int)
;
INSERT INTO TableB
(username, token)
VALUES
('user01', 1),
('user02', 2),
('user03', 3),
('user04', null)
;
--更新
update TableA T1
set token = T2.token
from TableB T2
where T1.username = T2.username;
--驗證
select * from TableA;
| username | token |
|----------|--------|
| user01 | 1 |
| user02 | 2 |
| user03 | 3 |
| user04 | (null) |
好哦!非常感謝回答,我決定分批來執行
恩~我應該都是這樣大量更新過去的~但幾百萬筆應該也幾秒鐘~
update TableA
set token = b.token
from TableA as a
left join TableB as b on a.username = b.username
但如果只是0變成1以上的話~是可以在增加條件~
update TableA
set token = b.token
from TableA as a
left join TableB as b on a.username = b.username
where a.token = 0
應該是
set token = b.token
不是
update token = b.token
另外，這種寫法似乎不是每個資料庫都支援，我的sqlite就不吃。
哈哈~我剛剛~有發現打錯~修正了~感謝提醒~
sqlite資料庫的話
使用的SQL語法Update，放置方式不太一樣~
https://stackoverflow.com/questions/19270259/update-with-join-in-sqlite
我的方式適用MSSQL
感謝。
sqlite的update好像不支援from子句，所以只能用sub query做(而且還要做二次，一次放在SET，一次放在WHERE)。
UPDATE tableA
SET token = (
SELECT token
FROM tableB
WHERE username = tableA.username
)
WHERE EXISTS (
SELECT username
FROM tableB
WHERE username = tableA.username
);
如果他要多個欄位同時修改~你會怎樣改??
我應該會用fuzzylee1688的方法，去alter table。
感謝各位回复,
目前使用可行哦,但是DB是postgresql
update customer as tableA
set token = tableB.token
from
(
select * FROM dblink('xxxxx','select username,token from customer') as t(username varchar,token numeric)
)
as tableB where tableA.username = tableB.username
UPDATE TableA
SET token=TableB.token
FROM TableB
WHERE username=TableB.username
在DW 批次作業環境下, 是不建議大量資料用update的,因為會同時寫入大量的 recovery log, 一般建議是先建立一work table, 再用insert join方式塞入, 然後做一次rename table, 用空間換取時間方式, 才不會長時間writei lock table, 影響online前端實用資料.
UPDATE A
SET A.token=B.token
FROM TableA A,TableB B
WHERE A.username=B.username
--where 後面可以再給過濾條件喔~
iThome鐵人賽
看影片追技術