错误地更新查询结果 [英] Update query resulting wrongly
问题描述
我有一个名为company_emp
的表.在该表中,我有6个与员工相关的列:
I have table called company_emp
. In that table I have 6 columns related to employees:
- empid
- ename
- dob
- doj,...
我还有一个名为bday
的表.因为我只有两栏;温和和多宝.
I have another table called bday
. In that I have only 2 columns; empid and dob.
我有这个查询:
select empid, dob
from company_emp
where dob like '01/05/2011'
它显示了一些雇员列表.
It shows some list of employees.
以同样的方式查询表bday,其中列出了一些员工.
In the same way I have queried with table bday it listed some employees.
现在,我要更新日期为'01/05/2011'的雇员的company_emp
表.
Now I want to update the company_emp
table for employees who have date '01/05/2011'.
我尝试过这样的查询:
update company_name a
set dob = (select dob from bday b
where b.empid=a.empid
and to_char(a.dob,'dd/mm/yyyy') = '01/05/2011'}
然后该行中的所有记录变为空.如何解决此查询?
Then all the records in that row becoming null. How can I fix this query?
推荐答案
您正在更新company_name/emp表中的每一行.
You're updating every row in the company_name/emp table.
您可以使用相关子查询来解决该问题,以确保该行存在,或者通过在bday.empid上放置主键或唯一键并进行查询来更有效地进行查询:
You can fix that with a correlated subquery to make sure the row exists, or more efficiently by placing a primary or unique key on bday.empid and querying:
update (
select c.dob to_dob,
d.dob from_dob
from company_emp c join dob d on (c.empid = d.empid)
where d.dob = date '2011-05-01')
set to_dob = from_dob
语法未经测试.
这篇关于错误地更新查询结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!