SOLVED: Updating IDs from different table using SQL

[Saint Nick]

We have two systems, Old IDs and New IDs. I also have a conversion table on the server that says certain Old IDs = New IDs. I am trying to update the old with the new through SQL.

Here is the code I run:

UPDATE DVMS_FNET_Tran_TEST
SET P8_Doc_ID = (
					SELECT NuID
					FROM DVMS_FNET_Tran_TEST as a, FileNet_ID_Conv as b
					WHERE a.Pan_Doc_ID = b.OldID
				)

It gives me the error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What exactly am I doing wrong? This makes the most sense to me and it is what I keep coming back to. I somehow got it to work yesterday, but now it is not.

 

[uclabachelor]

We have two systems, Old IDs and New IDs. I also have a conversion table no the server that says certain Old IDs = New IDs. I am trying to update the old with the new through SQL.

Here is the code I run:

UPDATE DVMS_FNET_Tran_TEST
SET P8_Doc_ID = (
                    SELECT NuID
                    FROM DVMS_FNET_Tran_TEST as a, FileNet_ID_Conv as b
                    WHERE a.Pan_Doc_ID = b.OldID
                )

It gives me the error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What exactly am I doing wrong? This makes the most sense to me and it is what I keep coming back to. I somehow got it to work yesterday, but now it is not.

You answered your own question.

 

[KIAman]

You either have multiple rows in your FileNet_ID_Conv table with duplicate OldID or DVMS_FNET_Tran_TEST with duplicate Pan_Doc_ID.

Try running your subquery alone to see which.

 

[mrjminer]

My guess: each of the tables you are pulling from has a column named NuID

If this is right, use a.NuID or b.NuID

 

[KIAman]

My guess: each of the tables you are pulling from has a column named NuID

If this is right, use a.NuID or b.NuID

That would throw an ambiguous column error.

 

[mrjminer]

That would throw an ambiguous column error.

Yea, I was thinking that too. I wasn't sure on the order the errors are reported in, though, since I've never had his problem; didn't know if it would throw a subquery error or ambiguous

 

[KB]

I have never done it that way. When I saw your query my first thought was that you didn't have the JOIN in the right way:

UPDATE a SET a.P8_Doc_ID = b.NuID
FROM DVMS_FNET_Tran_TEST As a INNER JOIN FileNet_ID_Conv As b ON a.Pan_Doc_ID = b.OldID

 

[Jeraden]

Your subquery isn't joining back to the table you are updating. Take DVMS_FNET_Tran_TEST out of your subquery and just have it reference the table you are updating instead...

UPDATE DVMS_FNET_Tran_TEST as a
SET a.P8_Doc_ID = (
SELECT NuID
FROM FileNet_ID_Conv as b
WHERE a.Pan_Doc_ID = b.OldID)

 

[Saint Nick]

Your subquery isn't joining back to the table you are updating. Take DVMS_FNET_Tran_TEST out of your subquery and just have it reference the table you are updating instead...

UPDATE DVMS_FNET_Tran_TEST as a
SET a.P8_Doc_ID = (
SELECT NuID
FROM FileNet_ID_Conv as b
WHERE a.Pan_Doc_ID = b.OldID)

I tried this at one point, but it doesn't like the "as" in line one.

 

[Saint Nick]

I managed to hose one of the tables while running a query (thankfully only test data). But, I was able to get the following query to run—which, now when I look back at it, is pretty much the same one I had to begin with:

UPDATE DVMS_FNET_Tran
SET P8_Doc_ID = (
					SELECT DISTINCT NuID
					FROM FileNet_ID_Conv as b
					WHERE DVMS_FNET_Tran.Pan_Doc_ID = b.OldID
				)

I ran this on a table that only had 40 rows of test data, and it worked. However, when running it on the production data (440,000 rows), it throws the error listed above in the thread:

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

I am beginning to think that the query can't handle that amount of processing, or, there may be duplicates while the query runs which makes the subquery return more than one result.

 

[Barnaby W. Füi]

edit: nevermind, I wasn't reading along closely enough.

 

[Jeraden]

Run this query:

SELECT b.OldID, count(0)
FROM DVMS_FNET_Tran_TEST as a, FileNet_ID_Conv as b
WHERE a.Pan_Doc_ID = b.OldID
group by b.OldID
having count(0) > 1

That should show you which records are returning multiple records in your subquery.

or count(distinct NuID) instead of count(0)...

 

[Saint Nick]

Run this query:

SELECT b.OldID, count(0)
FROM DVMS_FNET_Tran_TEST as a, FileNet_ID_Conv as b
WHERE a.Pan_Doc_ID = b.OldID
group by b.OldID
having count(0) > 1

That should show you which records are returning multiple records in your subquery.

or count(distinct NuID) instead of count(0)...

I actually ran a query similar to this to try to find any multiple returns, but the query shows no result :/

This is boggling my mind.

 

[GeekDrew]

What database engine are you using?

This query should return to you a list of records for which duplicates may exist within your original subquery:

SELECT OldID, COUNT(*) AS mycount
FROM FileNet_ID_Conv
GROUP BY OldID
HAVING COUNT(*) > 1

Do the results from that query differ at all from the results of this query?

SELECT OldID, COUNT(*) AS mycount
FROM FileNet_ID_Conv
GROUP BY OldID, NuID
HAVING COUNT(*) > 1

This is a different way to write your update query, which (off of the top of my head) I think would succeed even with duplicates in FileNet_ID_Conv, though if there are any duplicates identified above, this query could potentially compromise data integrity in DVMS_FNET_Tran_TEST.P8_Doc_ID.

UPDATE DVMS_FNET_Tran_TEST
SET P8_Doc_ID = b.NuID
FROM DVMS_FNET_Tran_TEST AS a
INNER JOIN FileNet_ID_Conv AS b ON a.Pan_Doc_ID = b.OldID

While I don't have any basis for including this thought, I can't help but mention that you should be watchful of NULL values in these result/update sets.

 

[Saint Nick]

Thank you so much GeekDrew! Your last SQL query worked perfectly and was exactly what I was looking for! 🙂