iT邦幫忙

0

SQL 把A資料表的值覆蓋到B資料表

sql
Ks 2018-05-21 01:29:047129 瀏覽

請問 我有兩個資料表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

Ks iT邦新手 3 級 ‧ 2018-05-21 12:38:28 檢舉
抱歉哦,各位久等回复,
我使用的是postgre資料庫,
目前使用
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
是可行的,請問這樣會有什麼問題嗎?
感謝各位回答
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
1
暐翰
iT邦大師 1 級 ‧ 2018-05-21 02:49:09
最佳解答

我不知道你的資料庫是什麼
先提供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) |

DEMO SQL Fiddle


你先看一下有問題再跟我說一下 :-)

看更多先前的回應...收起先前的回應...
Ks iT邦新手 3 級 ‧ 2018-05-21 12:46:52 檢舉

你好哦,我使用的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
是可行的,請問這樣會有什麼問題嗎?

暐翰 iT邦大師 1 級 ‧ 2018-05-21 13:04:10 檢舉

請問這樣會有什麼問題嗎?

有,而且很大問題
因為你的資料量有百萬筆資料,注意記憶體是否足夠!
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) |
        

DEMO SQL Fiddle

Ks iT邦新手 3 級 ‧ 2018-05-21 16:56:00 檢舉

好哦!非常感謝回答,我決定分批來執行/images/emoticon/emoticon33.gif

Ks iT邦新手 3 級 ‧ 2018-05-22 15:39:14 檢舉

再請教一下,2台postgresql 有辦法像mssql這樣
[server].[dbo].[table_name] 這樣互相調用嗎?
還是只能用dblink.

暐翰 iT邦大師 1 級 ‧ 2018-05-23 08:16:13 檢舉

mssql [server].[dbo].[table_name]
這也是dblink

Ks iT邦新手 3 級 ‧ 2018-06-12 12:35:34 檢舉

再请问mysql有什么办法可以join postgresql的资料进来呢?感谢
/images/emoticon/emoticon05.gif

0
純真的人
iT邦大師 1 級 ‧ 2018-05-21 09:24:21

恩~我應該都是這樣大量更新過去的~但幾百萬筆應該也幾秒鐘~

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

Ks iT邦新手 3 級 ‧ 2018-05-21 12:52:10 檢舉

感謝各位回复,
目前使用可行哦,但是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

0
richardsuma
iT邦大師 1 級 ‧ 2018-05-21 09:43:56

UPDATE TableA
SET token=TableB.token
FROM TableB
WHERE username=TableB.username

Ks iT邦新手 3 級 ‧ 2018-05-21 12:51:15 檢舉

目前使用可行哦,但是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

0
fuzzylee1688
iT邦研究生 3 級 ‧ 2018-05-21 11:30:38

在DW 批次作業環境下, 是不建議大量資料用update的,因為會同時寫入大量的 recovery log, 一般建議是先建立一work table, 再用insert join方式塞入, 然後做一次rename table, 用空間換取時間方式, 才不會長時間writei lock table, 影響online前端實用資料.

Ks iT邦新手 3 級 ‧ 2018-05-21 23:04:17 檢舉

了解,感謝回复!

0
小魚
iT邦大師 1 級 ‧ 2018-05-21 12:05:49

所以樓主到底是用哪一種資料庫??

Ks iT邦新手 3 級 ‧ 2018-05-21 12:42:23 檢舉

你好,抱歉遲回复 我使用的是postgresql

0
cocoyijou
iT邦見習生 ‧ 2018-05-22 11:45:53

UPDATE A
SET A.token=B.token
FROM TableA A,TableB B
WHERE A.username=B.username
--where 後面可以再給過濾條件喔~

Ks iT邦新手 3 級 ‧ 2018-06-12 12:33:34 檢舉

感谢

我要發表回答

立即登入回答