根据最新记录更新另一个表 [英] Update another table based on latest record

问题描述

我有下表(对不起，我不知道如何发布表格……粗体是字段名称)

I have the below table (sorry couldn't figure out how to post a table... in bold are the field names)

code desc 频道日期

1001 超市 10-oct

1001 A supermarket 10-oct

1001 B minimarket 15-dic

1001 B minimarket 15-dic

1003 餐厅 07-5 月

1003 A restaurant 07-may

1003 B bar 30-abr

1003 B bar 30-abr

1003 12-dic 餐厅

1003 A restaurant 12-dic

1002 B kiosk 10-oct

1002 B kiosk 10-oct

我正在尝试获取每个代码的最新记录，并在另一个表中更新它，该表中已经有我需要更新的所有代码(在这个表上我有相同的字段，但需要将它们更新为最新的)

I am trying to get the latest record for each code and update it in another table where I have already all the codes I need to be updated (on this table I have the same fields but needed to update them to the latest)

结果是这样

频道日期代码

1001 B minimarket 15-dic

1001 B minimarket 15-dic

1003 12-dic 餐厅

1003 A restaurant 12-dic

1002 B kiosk 1 0-oct

1002 B kiosk 1 0-oct

提前感谢您的帮助！

推荐答案

您可以使用查询获得结果:

You can get the results using a query:

select t.*
from table as t
where t.date = (select max(t2.date) from table as t2 where t2.code = t.code);

我不确定你的另一个表是什么样的，但你可以把它改成这样的查询:

I'm not sure what your other table looks like, but you could fix this into a query like:

update secondtable
    set val = (select channel
               from table as t
               where t.code = secondtable.code and
                     t.date = (select max(t2.date) from table as t2 where t2.code = t.code)
             );

如果设置了多个字段，您也可以使用 join.

You could also use a join if more than one field were being set.

这篇关于根据最新记录更新另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！