Is there a way to write an UPDATE statement to fix multiple rows with bad data?-CodePudding

I am trying to fix a data error in a SQL Server database.

There are values that are using the wrong partId from the wrong factory.

I can get a list of all the machines with wrong parts like this:

---machines using wrong parts from wrong factory 88 and 89:
SELECT ml.machineId, ml.machineName, mp.machineId, mp.partId, pc.officialIdentifierId 
FROM industry.machineParts mp
INNER JOIN partsCatalog pc ON mp.partId = pc.partId
INNER JOIN machineList ml ON mp.machineId = ml.machineId
WHERE pc.factoryId IN (88,89)

I have one ID that is common amoung them in the machineParts table and that is the officialIdentifierId ID.

But I am unsure of how to change all the rows in the machineParts table with partIds from factory 88 and 89 with the new factory 100 partIds.

The machineParts table looks like this:

machineId       partId
----------------------
7623741         123 
8927661         124
6471982         125

So if a machine has an old partId from factory 88 or 89, I need to replace it with the partId from factory 100.

The partsCatalog table has the factoryId value and looks like this:

partId  |   name        |   factoryId   |   officialIdentifierId
--------------------------------------------------------------
123           OilFil                88          A-00-900c     
124           O_Filter              89          A-00-900c
125           Oil Filter            100         A-00-900c

So the correct machineParts table should look like this after the fix:

machineId       partId
----------------------
7623741         125
8927661         125
6471982         125

I tried this update statement to fix all the bad rows:

UPDATE machineParts SET partID = (SELECT partID FROM  partsCatalog WHERE factoryId = 100)
WHERE partID IN(123, 124)

But I get this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

Is there a way to fix all the bad rows with one query?

Thanks!

CodePudding user response：

The problem here is that you have multiple records with factoryId = 100. You can "fix" this with a TOP 1 as below, but you must be certain that the partID is the same on all records with the subquery's factoryId:

UPDATE machineParts SET partID = (SELECT TOP(1) partID FROM  partsCatalog WHERE factoryId = 100)
WHERE partID IN(123, 124);

This says "just give me the partID on the first record with factoryId 100". You can also tweak the subquery to give you the most recent one, assuming there's a createdOn field as an example:

UPDATE machineParts SET partID = (SELECT TOP(1) partID FROM  partsCatalog WHERE factoryId = 100 ORDER BY createdOn DESC)
WHERE partID IN(123, 124);

CodePudding user response：

Is there only one part at factory 100 with an officialIdentifierId of A-00-900c?

If so,

UPDATE machineParts 
    SET partID = (SELECT partID 
                  FROM  partsCatalog 
                  WHERE factoryId = 100
                    AND officialIdentifierId = 'A-00-900c'
                 )
WHERE partID IN (123, 124)