更新查询结果错误

2021-12-30 00:00:00 oracle11g oracle

我有一个名为 company_emp 的表.在该表中，我有 6 列与员工相关:

I have table called company_emp. In that table I have 6 columns related to employees:

  1. empid
  2. 名字
  3. dob
  4. 司法部，...

我有另一个表叫做 bday.因为我只有 2 列；empid 和 dob.

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

语法未测试.

相关文章