Updating Multiple fields with Table Joins 1

[lynchg]

I have a work table that gets populated with data from a client feed. Their data (the client feed), does not contain the field we use internally to uniquely identify a person(CorpID). This data is for insurance info and the identifying numbers that the clients are familiar with is a 2 field identifier (SubscriberID-MemberNumber). Basically the SubscriberID identifies a family and the MemberNumber identifies the individual within the family.

Ex:
SubscriberID MemberNumber
Me 123456789 00
Wife 123456789 01
Child1 123456789 02
Child2 123456789 03

Unfortunately the client feed does not contain the MemberNumber value but does contain name, dob, and gender. I want to update the CorpID field in the work table by linking to our members table on the SubscriberID, first name, dob, and gender fields. I get this error:

"The result of a scalar fullselect, Select Into statement, or Values Into statement is more than one row. SQLSTATE 21000"

Here is my SQL:

UPDATE WORK_TBL SET (CorpID, MemberNumber) = (Select MEMBER.CorpID, MEMBER.MemberNumber From MEMBER INNER JOIN WORK_TBL On WORK_TBL.PatientDOB = MEMBER.MBR_BRTH_DATE AND WORK_TBL.PatientSex = MEMBER.MBR_SEX_CODE AND WORK_TBL.SubscriberID = MEMBER.MBR_SUB_ID And WORK_TBL.PatientFirstName = MEMBER.MBR_FRST_NAME)

This is DB2 v7.1 on OS/390.

 

[blom0344]

Just for a first check:

Does this work properly:

UPDATE WORK_TBL SET (CorpID, MemberNumber) = (Select distinct MEMBER.CorpID, MEMBER.MemberNumber From MEMBER INNER JOIN WORK_TBL On WORK_TBL.PatientDOB = MEMBER.MBR_BRTH_DATE AND WORK_TBL.PatientSex = MEMBER.MBR_SEX_CODE AND WORK_TBL.SubscriberID = MEMBER.MBR_SUB_ID And WORK_TBL.PatientFirstName = MEMBER.MBR_FRST_NAME)

T. Blom
Information analyst
tbl@shimano-eu.com

 

[lynchg]

No, the 'Distinct' didn't change things, same error. This is intended to update multiple rows all at once. I searched this forum and thought I had found the solution to my problem. That is where I got this update SQL from. Please don't tell me that updates are restricted to one row at a time.

 

[blom0344]

No, an update statement can update a table in one UOW, but there is something inherently wrong with your SQL.

In order to update a row the select part of the statement should return ONE result for every combination of (Corp_ID,MemberNumber).
Without the correlation you will try to update every record with the full select set and that is throwing the error-message.

In general:

UPDATE Table1
SET Table1.A = (SELECT Table2.A* FROM Table2 WHERE
Table1.X = Table2.Y AND /// CORRELATION
/// additional restrictions, joins /////
WHERE EXISTS .............

with the WHERE EXIST (optional) part to limit the range of updated fields

T. Blom
Information analyst
tbl@shimano-eu.com

 

[lynchg]

When I run just the select portion of the update query above, I just get 62 records. There are no duplicates of the combination of CorpID and MemberNumber, in fact there are no duplicates of CorpID.

I am attempting to update WORK_TBL which has 64 records, apparently there is no match for 2 of my records. I understand what you are saying about there only being one record in the select for each record that needs to be updated. It doesn't seem like I should be having this type of problem. I am not sure what to do next.

 

[blom0344]

Okay, this being a monday I am not that sharp yet.

I would say that for every combination of

WORK_TBL.PatientDOB
WORK_TBL.PatientSex
WORK_TBL.SubscriberID
WORK_TBL.PatientFirstName

One or none set from the MEMBER table should be returned.
If there is just one combination that fetches 2 records from MEMBER the update will fail as a whole.

But then again, maybe OS/390 is different in some way where I am used to. Time to kick a guru I would say....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[lynchg]

I understand that just one dupe set would cause a problem. That is not the case here, I have checked and rechecked the output of the select portion of my update SQL, and included the SubscriberID, name, dob, and sex code. There is no duplication of that 4 field combination. I have 2 sets of duplicate SubscriberID values but the values in all of the other fields for each of these pairs are different. I only have 62 records of out put so it is easy to visually scan the results by ordering on SubscriberID.

 

[MarcLodge]

Humour me here, but try changing the format of the update to:
UPDATE WORK_TBL SET (CORPID, MEMBERNUMBER) =
(SELECT MEMBER.CORPID, MEMBER.MEMBERNUMBER
FROM MEMBER
,WORK_TBL
WHERE WORK_TBL.PATIENTDOB = MEMBER.MBR_BRTH_DATE
AND WORK_TBL.PATIENTSEX = MEMBER.MBR_SEX_CODE
AND WORK_TBL.SUBSCRIBERID = MEMBER.MBR_SUB_ID
AND WORK_TBL.PATIENTFIRSTNAME = MEMBER.MBR_FRST_NAME)

which is if I have read your SQL correctly, what you are trying to do

 

[lynchg]

I tried that too. Same error.

 

[sathyarams]

Well, I use my own tables and apply my understanding of lynchg's problem on them ...


tupd1 (all k's are null):
i j k
-------------
1 1
2 2
3 3
4 4

tupd2 :
i j k
-----------
1 1 5
2 2 6
3 3 9

Statement 1 (similar to lynchg's)

update tupd1 set k=(select tupd2.k from  tupd1 inner join tupd2 on tupd1.i=tupd2.i and tupd1.j=tupd2.j)

I get -811

Statement 2 :

update tupd1 set k=(select tupd2.k from  tupd2 where tupd1.i=tupd2.i and tupd1.j=tupd2.j)

4 rows are updated and now my tupd1 is
i j k
-------------------
1 1 5
2 2 6
3 3 9
4 4

In statement 1, the resultset of the inner SELECT is applied to every row in tupd1

In statement 2, tupd2's k will be returned only if tupd1(i,j) are equal to tupd2(i,j) ...

Remember, statement 2 will fail, if there is more than 1 row returned for each (i,j) value and distinct on k will work only under special circumstances ...

Applying this to the orignal problem ,

UPDATE WORK_TBL SET (CORPID, MEMBERNUMBER) =              
  (SELECT MEMBER.CORPID, MEMBER.MEMBERNUMBER              
   FROM MEMBER                                            
   WHERE WORK_TBL.PATIENTDOB = MEMBER.MBR_BRTH_DATE       
   AND   WORK_TBL.PATIENTSEX = MEMBER.MBR_SEX_CODE        
   AND   WORK_TBL.SUBSCRIBERID = MEMBER.MBR_SUB_ID        
   AND   WORK_TBL.PATIENTFIRSTNAME = MEMBER.MBR_FRST_NAME)

I guess will work ...

BTW, this works on Version 8 of both Windows and zOS ...

Hope I have understood the original problem .. My apologies if I haven't ...


An additional where clause somewhere in the statement can help in eliminating update on all rows in tupd1 and update only the 'eligible' rows , but am too tired at half-past-ten at night to think ...






For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[lynchg]

Thank you for all of your input, thanks to these posts and some others, I was able to try several combinations of good guesses until I hit upon the right syntax. blom0344 was close with the Exists clause and subselect which apparently works to align which value from the source data updates each record in the destination table. Here is the SQL that worked for me.

UPDATE WORK_TBL SET (CorpID, MemberNumber) = (Select Distinct MEMBER.CORPID, MemberNumber From MEMBER Where WORK_TBL.PATIENTDOB = MEMBER.MBR_BRTH_DATE AND WORK_TBL.PATIENTSEX = MEMBER.MBR_SEX_CODE AND WORK_TBL.SUBSCRIBERID = MEMBER.MBR_SUB_ID And WORK_TBL.PATIENTFIRSTNAME = MEMBER.MBR_FRST_NAME And Exists (Select 1 From MEMBER Where WORK_TBL.PATIENTDOB = MEMBER.MBR_BRTH_DATE AND WORK_TBL.PATIENTSEX = MEMBER.MBR_SEX_CODE AND WORK_TBL.SUBSCRIBERID = MEMBER.MBR_SUB_ID And WORK_TBL.PATIENTFIRSTNAME = MEMBER.MBR_FRST_NAME))

 

[blom0344]

Man, I am actually relieved you pulled that off in the end.

It stuck in the back of my head for the greater part of the past 24 hours.

My experience with the exists clause is that you can RESTRICT the range of records that will be updated, so I am still puzzled why it did not work with the syntax earlier offered.........



T. Blom
Information analyst
tbl@shimano-eu.com

 

[lynchg]

Who says it has to make sense, it's DB2.

Thanks for your help.

 

[ansonee]

I've got a similar issue. Below is my update statement which returns the multiple record error. I've tried some permutations based on this post but can't quite get it.

P1: BEGIN
UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP));
END P1

Any suggestions would be greatly appreciated!!

Thank you!!

 

[Agro42]

Change this bit:

WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
   AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM

to

WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID IN (SELECT
   AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM