Update statement isusue and error 7547

Database

Update statement isusue and error 7547

Hi. When running the following code, I keep getting the error 7547 message, but both of tables I used for this update have no duplicate rows:

 

UPDATE A

FROM TEMPA AS A

(SELECT DISTINCT STUDY_ID, TESTDATE, TESTNAME, CODE FROM TEMPB) AS B

SET TESTDATE=B.TESTDATE,

        TESTNAME=B.TESTNAME,

        CODE=B.CODE

WHERE A.STUDY_ID=B.STUDY_ID;

 

Anyway to solve this issue. It annoying it won't do update statment yet there no duplicate rows in either table?

 

3 REPLIES

Re: Update statement isusue and error 7547

Hi,

 

This error has nothing to do with duplicate rows in the table.

 

This happens because of the relationship between the two tables based on column STUDY_ID (the join criteria).

 

It looks like you have at least one row in the target table which joins to multiple rows in the target table.

(And using the derived table with DISTINCT won't help in that situation because that does a 'distinct' operation across the entire row, NOT just on the first column.)

 

Firstly, satisfy yourself that your data fits the situation that I've described.

 

Then you need to decide how to handle it. Can you change the join criteria such that you get a 1:1 relationship between the rows being joined?

Do you need to have a different derived table (dt) such that each STUDY_ID value is only returned once by the dt?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Update statement isusue and error 7547

I decided to subset the update statment for each individual variable. I got updating testdate and code to work, but for some reason the update for testname is not working. Here is the revise code, but I'm not sure why it working for other two variables, but not testname:

 

UPDATE A

FROM TEMPA AS A

(SELECT DISTINCT STUDY_ID, TESTNAME FROM TEMPB) AS B

SET TESTNAME=B.TESTNAME

WHERE A.STUDY_ID=B.STUDY_ID;

 

I should point out the TESTNAME in table TEMPA is blank and I'm trying to fill it in using data for TESTNAME in table TEMPB. But nothing is working.

Re: Update statement isusue and error 7547

Hi,

 

It probably isn't the content of TEMPA (the target table) that is causing this problem, it is the data in TEMPB.

 

Specifically, I think you've got multiple values for 'Testname' for a single value of Study_ID.

Try the following to prove this:

SELECT STUDY_ID
  ,COUNT(DISTINCT(TESTNAME)) as testname_count
FROM TEMPB
GROUP BY 1
HAVING testname_count > 1;

If you get any rows back from that query those are the Study_ID values that are causing this error.

 

As I said earlier you need to either:

1) change your derived table  to generate a single row for each Study_ID value,

2) change the join criteria

3) instead of updating a table build a new one - but be aware that you'll end up with multiple rows on the new table for some single rows on the current TEMPA.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com