SQL教學 – Cewolf教學網

升級 Odoo 版本有很多地方需要用到 SQL 語法，以下是更新時的紀錄

從表 account_invoice 搜尋欄位 number 中 SCNJ 開頭得記錄

select * from account_invoice where number like 'SCNJ%'

將表 account_invoice 中欄位 number 中的 SCNJ 字元替換為 RSAJ

update account_invoice set number = replace(number,'SCNJ','RSAJ')

範例：

select * from account_invoice where number like 'SCNJ%'
355
update account_invoice set number = replace(number,'SCNJ','RSAJ') 
select * from account_invoice where number like 'RSAJ%'
355

select * from account_invoice where internal_number like 'SCNJ%'
259
update account_invoice set internal_number = replace(internal_number,'SCNJ','RSAJ') 
select * from account_invoice where internal_number like 'RSAJ%'
259

select * from account_invoice where move_name like 'SCNJ%'
221
update account_invoice set move_name = replace(move_name,'SCNJ','RSAJ') 
select * from account_invoice where move_name like 'RSAJ%'
221

select * from account_invoice where number like 'ECNJ%'
34
update account_invoice set number = replace(number,'ECNJ','REXJ') 
select * from account_invoice where number like 'REXJ%'
34

select * from account_invoice where internal_number like 'ECNJ%'
28
update account_invoice set internal_number = replace(internal_number,'ECNJ','REXJ') 
select * from account_invoice where internal_number like 'REXJ%'
28

select * from account_invoice where move_name like 'ECNJ%'
35
update account_invoice set move_name = replace(move_name,'ECNJ','REXJ') 
select * from account_invoice where move_name like 'REXJ%'
35

進行 OpenUpgrade 升級時，執行下面這行，跳出了有很多重複資料的錯誤訊息

ALTER TABLE "mail_followers" ADD CONSTRAINT "mail_followers_mail_followers_res_partner_res_model_id_uniq" unique(res_model,res_id,partner_id)

唯一的解決方法就是刪除重複的資料

但資料庫資料量龐大，此時可以使用下列語法來快速篩選

select column1, column2, column3, count(*) from Table group by column1, column2, column3 having count(*) > 1;

例如
select res_model, res_id, partner_id, count(*) from mail_followers group by res_model, res_id, partner_id having count(*) > 1;

就會顯示出哪些資料的重複次數了

刪除的部分，目前還沒有找出合適的方案