如何编写具有表之间的某些连接的更新查询

Question

我如何为这个SQL (Oracle)写一个update查询，我想做一个使用多个表(其中3个)的update如下所示：

   update payroll.emp_appointments
   set e.sales=w.sales
   where the details below

   select w.storecode,w.salespersoncode,w.sumsales,w.truncateddate, c.customerid, 
   e.firstname,e.lastname,c.contactid
   from weekstat w, sysdba.contact_retail c, payroll.emp_appointments e where 
   c.customerid=w.customerid
   and cast(w.salespersoncode  as char(6))=e.emp_num
   and trunc(e.tradingdate)=w.truncateddate
   and e.contactid=c.contactid
   and e.storecode=cast(e.storecode as varchar2(6))
   and e.sales=0.00
   and e.tradingdate > sysdate-10

在创建临时表之后，我尝试执行以下操作，但它似乎更新了emp_appointments表中的所有行，这是我不想做的：

   update payroll.emp_appointments e
   set e.sales= (
   select ue.sumsales
   from update_emp_sales_temp ue
   where e.contactid=ue.contactid and
   e.emp_num=ue.salespersoncode and 
   e.storecode=ue.storecode and
   e.firstname=ue.firstname and 
   e.lastname=ue.lastname and
   trunc(e.tradingdate)=ue.truncateddate)
   where e.tradingdate>sysdate-30;

Answer 1

它更容易记住结构

update <select with all the joins> set <set conditions>

在您的案例中：

UPDATE
(select w.sales src, e.sales dst
from weekstat w, sysdba.contact_retail c, payroll.emp_appointments e where 
c.customerid=w.customerid
and cast(w.salespersoncode  as char(6))=e.emp_num
and trunc(e.tradingdate)=w.truncateddate
and e.contactid=c.contactid
and e.storecode=cast(e.storecode as varchar2(6))
and e.sales=0.00
and e.tradingdate > sysdate-10)
SET dst = src

只需记住，您只需要更新一个表

使用合并的另一种方法：

MERGE INTO payroll.emp_appointments e
USING (select w.sales, e.emp_num
from weekstat w, sysdba.contact_retail c, payroll.emp_appointments e where 
c.customerid=w.customerid
and cast(w.salespersoncode  as char(6))=e.emp_num
and trunc(e.tradingdate)=w.truncateddate
and e.contactid=c.contactid
and e.storecode=cast(e.storecode as varchar2(6))
and e.sales=0.00
and e.tradingdate > sysdate-10) subquery
ON (e.emp_num = subquery.emp_num)
WHEN MATCHED THEN UPDATE SET e.sales = subquery.sales;

我没有足够的信息来详细介绍你的逻辑来修复上面的查询，所以这里是第三种选择:) (它也可能失败，更多的信息在底部)：

UPDATE payroll.emp_appointments e
SET 
sales = (select w.sales from weekstat w, sysdba.contact_retail c
where 
c.customerid=w.customerid
and cast(w.salespersoncode  as char(6))=e.emp_num
and trunc(e.tradingdate)=w.truncateddate
and e.contactid=c.contactid) 
)
WHERE e.sales=0.00
and and e.tradingdate > sysdate-10
and (e.emp_num, trunc(e.tradingdate), e.contactid)
in 
(select w.salespersoncode, w.truncateddate, c.contactid from 
weekstat w, sysdba.contact_retail c
where c.customerid=w.customerid)

我不喜欢它，因为你基本上重复了两次逻辑。您可能会在select after 'sales =‘中得到一个错误，即它没有返回小数位数值，但我没有足够的信息来解决这个问题。因此，您有责任确保select为表e中要更新任何行恰好返回1行。

此外，我删除了看起来多余的条件and e.storecode=cast(e.storecode as varchar2(6))。

Answer 2

谢谢Vav，我尝试了以下方法，现在还起作用：

UPDATE payroll.emp_appointments e
SET (e.sales) = (
SELECT w.sumsales
FROM weekstat w, sysdba.contact_retail c
where 
    c.customerid=w.customerid
   and cast(w.salespersoncode  as char(6))=e.emp_num
   and trunc(e.tradingdate)=w.truncateddate
   and e.contactid=c.contactid
   and e.storecode=cast(e.storecode as varchar2(6))
   and e.sales=0.00
   and e.tradingdate > sysdate-10)

where exists (SELECT w.sumsales
FROM weekstat w, sysdba.contact_retail c
where 
    c.customerid=w.customerid
   and cast(w.salespersoncode  as char(6))=e.emp_num
   and trunc(e.tradingdate)=w.truncateddate
   and e.contactid=c.contactid
   and e.storecode=cast(e.storecode as varchar2(6))
   and e.sales=0.00
   and e.tradingdate > sysdate-10)