SQL联表进行更新与删除（使用联表条件）

联表删除

语法：

　　delete t1.* from t1, t2 where condition

案例：

　　由于程序bug，导致对账单生成了重复的对账单条目数据，现在需要删除掉重复数据。

　　如何判断重复：对账条目表的对账单号相同、来源info_no相同的多条记录视为一条有效记录，存在重复。

 

　　sql:

　　

DELETE t1.*
FROM
    verify_account_item t1,
    (
        SELECT
            verify_account_no,
            info_no,
            max(item_no) AS item_no
        FROM
            verify_account_item
        GROUP BY
            verify_account_no,
            info_no
        HAVING
            count(*) > 1
    ) t2
WHERE
    t1.item_no != t2.item_no
AND t1.verify_account_no = t2.verify_account_no

 

 

联表更新：

语法1（表连接写法）：

　　update t1, t2  set t1.col1 = t2.col2 where condition

案例：

　订单表新增了父单号，原来的对账信息里面也新增了父单号，但是数据没有清洗进去，因此这里进行数据清洗。

sql：

　　

update
    app_order_info t1
INNER JOIN verify_account_pool_info t2 ON t1.order_num = t2.sale_order_no
set t2.f_sale_order_no = t1.parent_order_id
where t1.parent_order_id is not null;

 

　

 

语法2(子查询写法)：

　　update t1 set t1.col1 = (select col2 from t2 where col3 = t1.col3) 

案例：

　　客商档案表新增了两个字段，冗余客商信息方便查询，这里需要进行数据清洗，写入初始数据

sql：

　　

update bd_cumandoc set
 `resp_user_name` = (select PSNNAME from bd_user where PK_PSNDOC = bd_cumandoc.pk_resppsn1),
`resp_dept_name` = (select DEPTNAME from bd_dept where PK_DEPTDOC = bd_cumandoc.pk_respdept1) 
where     pk_respdept1 IS NOT NULL
AND pk_resppsn1 IS NOT NULL;

 

注意，单表和表关联是同一张表，产生套娃情况修改和删除可能会报错，错误原因是不能在where条件依据进行更改的表作为条件。这个时候，可以使用临时表作为介质辅助删除。

例子sql：

CREATE TEMPORARY TABLE temp_Item_tab(
    item_id varchar(50) not null
);
insert into temp_Item_tab (item_id)
select sale_agreement_item_id from app_sale_agreement_item where sale_agreement_item_id not in (
    select min(sale_agreement_item_id) as itemId from app_sale_agreement_item 
    group by sale_agreement_id, product_id, customer_id
);

delete t1.*
FROM
    app_sale_agreement_item t1,
    temp_Item_tab t2
WHERE
    t1.sale_agreement_item_id = t2.item_id;