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

Question

我如何为这个SQL (Oracle)写一个更新查询我刚刚写在下面，我想使用多个表(其中3个)执行如下更新操作:

   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:

的方法

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 '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)