MySQL-如何使用另一个表中的值更新表? [英] MySQL - How can I update a table with values from another table?

问题描述

我的任务是修复mysql数据库中的一些无效数据.在一个表中，有缺少日期的人，应该从第二个表中填写，如果中有相应的条目.

I have the task to repair some invalid data in a mysql-database. In one table there are people with a missing date, which should be filled from a second table, if there is a corresponding entry.

TablePeople:ID，MissingDate，...
TableEvent:ID，people_id，replacementDate，...

TablePeople: ID, MissingDate, ...
TableEvent: ID, people_id, replacementDate, ...

Update TablePeople 
   set missingdate = (select replacementDate 
                        from TableEvent 
                       where people_id = TablePeople.ID)   
where  missingdate is null  
  and (select count(*) 
         from TableEvent 
        where people_id = TablePeople.ID) > 0

肯定不起作用. SQL还有其他方法吗?或者如何在mysql中处理单行以完成它?

Certainly doesn't work. Is there any other way with SQL? Or how can I process single rows in mysql to get it done?

推荐答案

我们需要有关无法正常工作的详细信息，但我认为您只需要使用:

We need details about what's not working, but I think you only need to use:

UPDATE TablePeople 
   SET missingdate = (SELECT MAX(te.replacementDate)
                        FROM TABLEEVENT te
                       WHERE te.people_id = TablePeople.id)   
 WHERE missingdate IS NULL

注释

• 由于担心您会从子查询中获取多个值，因此使用MAX来返回最新的替换日期
• 如果TABLEEVENT中没有支持记录，则它将返回null，因此没有变化

Notes

• MAX is being used to return the latest replacementdate, out of fear of risk that you're getting multiple values from the subquery
• If there's no supporting record in TABLEEVENT, it will return null so there's no change

这篇关于MySQL-如何使用另一个表中的值更新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！