mysql資料庫之批量更新文法

1.insert into ...on duplicate key update批量更新

2.replace into 批量更新

3.臨時表

SELECT * FROM `makemoneyinfo` where dakuanaccount = accountnumber order by creationtime 

select no,bank2 from sap_cw_notice where no in(

SELECT bankid FROM `makemoneyinfo` where dakuanaccount = accountnumber )

dakuanaccount(财務賬号)

update  makemoneyinfo set dakuanaccount = 

case when no = '' then ''  end,

dakuanaccount = 

end

--批量更新 列的位置 ，從一個表導入另一個（隻關心列的位置）

replace into makemoneyinfo( name, id) select rname, rtitle, rmood from tb2;

建立臨時表，先更新臨時表，然後從臨時表中update

select * from tmp;

-- 建立臨時表

CREATE TEMPORARY TABLE tmp ( NO VARCHAR ( 100 ) PRIMARY KEY, bank2 VARCHAR ( 50 ) );

--插入資料 

INSERT INTO tmp SELECT NO

,

bank2 

FROM

    sap_cw_notice 

WHERE

    NO IN ( SELECT bankid FROM `makemoneyinfo` WHERE dakuanaccount = accountnumber );

    -- 更新目标表資料

UPDATE makemoneyinfo,

tmp 

SET makemoneyinfo.dakuanaccount = tmp.bank2 

WHERE

    makemoneyinfo.bankid = tmp.NO;

4.使用mysql 自帶的語句建構批量更新

參考連結：

https://blog.csdn.net/h330531987/article/details/79114563

https://www.cnblogs.com/PatrickLiu/p/6385167.html

https://www.cnblogs.com/c-961900940/p/6197878.html

https://www.runoob.com/sql/sql-insert-into-select.html