7547: Target row updated by multiple source rows

Database
Enthusiast

7547: Target row updated by multiple source rows

Hi,

I am getting an error while doing this update.

UPDATE TGT
FROM TestDB.Table1 TGT,
TestDB.Table2 STG
SET
COL1 = STG.COL1
COL2 = STG.COL2
COL3 = STG.COL3
COL4 = STG.COL4
COL5 = STG.COL5

WHERE TGT.COL1='12345'
AND STG.COL5 = 'UPDATE'

The error is "7547: Target row updated by multiple source rows".
Can anyone can help me in resolving this?

Mani
3 REPLIES
Enthusiast

Re: 7547: Target row updated by multiple source rows

Your STG table contains more than 1 row that matches your criteria of STG.COL5='UPDATE'. If I had to guess, I'd imagine you need some sort of join between your STG and TGT tables, which the query you've posted does not have. Is there a common PK between the two? Or some kind of FK relationship?
Enthusiast

Re: 7547: Target row updated by multiple source rows

No. It does not have any kind of PK/FK relationship..
Enthusiast

Re: 7547: Target row updated by multiple source rows

Then how do you know which row to update in the TGT table? With the query above, here's a summary of what should be happening.

1. Filter Table1 to only rows where COL1='12345'.
2. Filter Table2 to only rows where COL5='UPDATE'.
3. Cartesian join the results of step 1 and 2
4. Update COL1,2,3,4,5 in the results of step 1 with ALL possible values from the results of step 2.

If either step 1 or 2 have multiple rows, you'll get some kind of error. Either a duplicate row/key error (depending on uniqueness constraints) or the 7547 error you got.