更新记录，但每个重复记录仅限制为一行 [英] Updating records but limiting to only one row per duplicate record

问题描述

我有一个表，其中包含许多item_number记录，这些记录在我的表中是重复的，而表中每个item_number的其他列都是空白.

I have a table with many item_number records which are duplicates within my table and the other columns within the table for each item_number are blank.

表中的所有记录均在g024的产品组下.但是，当我运行查询时，它将以此更新我的所有商品编号.

All of the records within the table are under product group of g024. However when I run my query it will update all of my items numbers with this.

我想要对每个item_number只填充一次资源代码，操作和操作描述，而不用查询中的条件填充每个item_number的重填空白字段.

What I want it to do is for each item_number only populate resource code, operation and operation description only once and not populating the remianing blank fields for each item_number with the criteria within the query.

稍后，我将使用其他更新查询填充这些其他黑色字段.

I will populate these other black fields with a different update query later on.

UPDATE resource1 SET [resource code] ='TOOLASSY', OPERATION = '10',   OPERATION_DESCRIPTION     = 'MOULD TOOL ASSEMBLY'
WHERE [product group]="G024" 

我确实尝试过类似的方法，以使查询仅将每个item_number更新一次.我调查了TOP关键字，但不确定该关键字是否在正确的行上.

I did try something like this to make the query only update each item_number only once. I looked into the TOP keyword but not sure if it was on the right line or not.

在子查询中，我需要像DISTINCT关键字这样的更新查询.

I need something like the DISTINCT keyword within a sub query for an update query.

UPDATE resource1 SET [resource code] ='TOOLASSY', OPERATION = '10', OPERATION_DESCRIPTION = 'MOULD TOOL ASSEMBLY'
WHERE [product group]="G024" AND ITEM_NUMBER IN(SELECT TOP 1 ITEM_NUMBER FROM RESOURCE1)

推荐答案

已更新: UPDATE resource1 SET [resource code] ='TOOLASSY', OPERATION = '10', OPERATION_DESCRIPTION = 'MOULD TOOL ASSEMBLY' WHERE [product group]="G024" AND [num] IN (SELECT MinNum FROM (SELECT resource1.[product group], resource1.[item_number], MIN(resource1.[num]) AS MinNum FROM resource1 GROUP BY resource1.[product group], resource1.[item_number]) Q1 WHERE Q1.[product group]=resource1.[product group] AND Q1.[item_number]= resource1.[item_number]);

Updated: UPDATE resource1 SET [resource code] ='TOOLASSY', OPERATION = '10', OPERATION_DESCRIPTION = 'MOULD TOOL ASSEMBLY' WHERE [product group]="G024" AND [num] IN (SELECT MinNum FROM (SELECT resource1.[product group], resource1.[item_number], MIN(resource1.[num]) AS MinNum FROM resource1 GROUP BY resource1.[product group], resource1.[item_number]) Q1 WHERE Q1.[product group]=resource1.[product group] AND Q1.[item_number]= resource1.[item_number]);

在MSAccess 2010中经过测试.

Tested in MSAccess 2010.

这篇关于更新记录，但每个重复记录仅限制为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！