更新行中具有相同ID但不同数据的数据

I have a record with same ID but different data in both rows While updating the final result should be the last record of that ID present in data. Example

我有一个具有相同ID但两行中数据不同的记录虽然更新最终结果应该是数据中存在的该ID的最后一条记录。例

ID | Name | PermanentAddrss | CurrentLocation
1  |  R1  | INDIA           |    USA
1  |  R1  | INDIA           |    UK

Now for ID 1 the record which will be loaded in database

现在为ID 1记录将加载到数据库中

1|R1|INDIA|UK

How this can be done in SQL server for multiple records?

如何在SQL Server中为多个记录完成此操作？

2 个解决方案

#1

You can identify the latest ID value by:

您可以通过以下方式识别最新的ID值：

SELECT B.ID, A.NAME, A.PERMANENTADDRS, A.CURRENTLOCATION
FROM
(SELECT ID, NAME, PERMANENTADDRS, CURRENTLOCATION, MAX(RNUM) AS LATEST_ID FROM
    (SELECT ID, NAME, PERMANENTADDRS, CURRENTLOCATION, ROW_NUMBER() OVER (PARTITION BY ID) AS RNUM FROM YOUR_TABLE)
GROUP BY ID, NAME, PERMANENTADDRS, CURRENTLOCATION) A
INNER JOIN
YOUR_TABLE B
ON A.LATEST_ID = B.ID;

This will take the last populated record for a given ID value. If the logic for latest record is different, it can be appropriately incorporated in the query.

这将采用给定ID值的最后填充记录。如果最新记录的逻辑不同，则可以将其适当地合并到查询中。

#2

Please understand that SQL server does not store or fetch data in order of data insertion, so to find the latest/last record you should have some way to order the records. This is typically a timestamp column like last_modified_date. Your current table is prime candidate for a slow changing dimension type 2; and you should consider implementing it. See explanation on Kimball's group site.

请理解SQL服务器不按数据插入顺序存储或获取数据，因此要查找最新/最后一条记录，您应该有一些方法来订购记录。这通常是一个时间戳列，如last_modified_date。您当前的表格是缓慢变化维度类型2的主要候选者;你应该考虑实施它。请参阅Kimball小组网站上的说明。

If you are really not affected by any order and just need a row for each id you can try below query.

如果您确实不受任何订单的影响，并且每个ID只需要一行，您可以在下面查询。

select 
ID, 
Name,
PermanentAddress,
CurrentLocation
from
(select
*,
row_number() over(partition by id order by (select null)) r
from yourtable)t
where r=1

#1