SQL Update Problem 3

[lynchg]

I get an error message with a SQLState = 42920, which says that you can't have a Group By clause in an embedded Select statement. My problem is that I can have multiple tbl2.fldB values for each tbl2.fldA value. If I don't restrict it somehow I get an error that the subselect is returning multiple values. I am not sure how to frame the SQL to get what I want.

Here is my SQL as I am currently trying to run it.

UPDATE tbl1
SET (tbl1.fldA, tbl1.fldB) =
(SELECT tbl2.fldA, MIN(tbl2.fldB)
FROM tbl2
WHERE tbl2.fldC = tbl1.fldC
AND tbl2.fldD = tbl1.fldD
AND tbl2.fldE = tbl1.fldE
AND tbl2.fldF = tbl1.fldF
AND Exists
(SELECT 1 From tbl2
WHERE tbl2.fldC = tbl1.fldC
AND tbl2.fldD = tbl1.fldD
AND tbl2.fldE = tbl1.fldE
AND tbl2.fldF = tbl1.fldF)
GROUP BY tbl2.fldA);

 

[MarcLodge]

lynchg,
Are there any WHERE clauses in the UPDATE? It looks to me as if you are trying to update every row in the table. Is that correct, or have I mis-read the SQL?

The inner sub-select must only return one value, otherwise as you state, it will error as it is trying to update one row (from the outer update statement) with multiple values from the inner.

I think that you might need to apply the same sort of WHERE clause to both the inner and the outer queries in order to get what you require.

Get back to us if you are still having problems. If you can show a bit of data, and what you are trying to achieve, that will help greatly.

Marc

 

[lynchg]

I do want to update every record in tbl1.

I can't show you actual data because this is health care info but I will try to describe what I am doing.

My tbl1 is a patient list work table that gets emptied out and repopulated every day. It is populated from files that come from doctors' offices (the list of their patients for the next day). We have some internal identifiers that aren't in the files from the doctors.

We match on the fields that are on those files (from the doctors), to the corresponding fields in our membership table (tbl2). Based on matching those 4 fields (fldC, fldD, fldE, & fldF), we want to populate the 2 internal identifying fields (fldA & fldB). We then use those internal fields to gather all kinds of data from many different tables to create a report to send back to the doctors.

My problem is that we have some cases where there are multiple combinations of fldA & fldB in our member table, so I would like to group by fldA, and take the MIN of fldB, so that I only return one record per person with my subselect.

I hope this makes sense.

 

[lynchg]

My question is asking if it is possible to do what I describe within one update statement. I have thought of other ways to do this in multiple steps. Like creating a temp table, populating it with my subselect, and then using that table to update my tbl1. But that adds extra objects, and causes more people (dba's), to get involved, and my boss would like to avoid that if possible.

Thanks

 

[MarcLodge]

lynchg,

Have your tried putting the sub-select in a temporary table within the SQL? I'm not sure whether you will still run across the "can't have a group by in a sub-select" error doing it this way or not. I suspect that it will possibly complain, but it's worth a shot. Something like:

UPDATE tbl1 
SET (tbl1.fldA, tbl1.fldB) =
SELECT firstcol, secondcol FROM
(SELECT tbl2.fldA AS firstcol, MIN(tbl2.fldB) AS secondcol
 FROM tbl2
 WHERE tbl2.fldC = tbl1.fldC
  AND tbl2.fldD = tbl1.fldD
  AND tbl2.fldE = tbl1.fldE
  AND tbl2.fldF = tbl1.fldF
  AND EXISTS 
(SELECT 1 From tbl2
 WHERE tbl2.fldC = tbl1.fldC
  AND tbl2.fldD = tbl1.fldD
  AND tbl2.fldE = tbl1.fldE
  AND tbl2.fldF = tbl1.fldF)
 GROUP BY tbl2.fldA)
AS TEMP_TAB

Unfortunately I can't test this where I work at the moment as they don't allow you to create your own tables. I am therefore unable to play with it, so it's given to you completely untested, for which I apologise. You may not need the AS TEMP_TAB line on the bottom, and it may, as mentioned earlier, not get round the GROUP BY error.

Let us know how you get on.

Marc

 

[lynchg]

I decided to break up my update into 2 separate parts. I first update one field (fldA), then I can use that value as one of my fields in the join on the 2nd update (fldB).

I am now getting what I need.

It is interesting in that the code that bombed has been in production for about 18 months and has not failed yet. I discovered this problem while doing some volume testing for a related process that we will move to production next month.

We got away with it so far because the data problem is rare (only exists for a few hundred people out of 2.5 million members), and our existing process only dealt with a few hundred people per day.

Marc, thanks for your help with this.

 

[MarcLodge]

lynchg,
For reference purposes sake, do you know whether the instream select that I coded would cause the group by problem that you originally had, or whether it would work or not?

Just curious, that's all!

Marc

 

[BrianTyler]

Surely the original problm was caused by the use of the 'Group By' clause. This is just not necessary in an 'exists' sub-query, and should be omitted because the only only requirement is to look for the existence of the rows and not the number or sum of their attributes.

 

[MarcLodge]

Brian,

I think you've fallen into the trap that I did when I first looked at it: The GROUP BY is outside of the EXISTS

Marc

 

[lynchg]

I have tried using your SQL to create a temp table but I can't get it to work.

Here is my SQL:

UPDATE dba1.appt_temp_tbl
SET (corp_mbr_id, MEMBERNUMBER) =
(SELECT corp_mbr_id, MBR_DEP_NBR FROM
(SELECT M.corp_mbr_id, MIN(M.MBR_DEP_NBR) AS DepNum
FROM dba1.member M
WHERE M.MBR_BRTH_DATE = ATT.PATIENTDOB
AND M.MBR_SEX_CODE = ATT.PATIENTSEX
AND M.MBR_SUB_ID = ATT.SUBSCRIBERID
AND M.MBR_FRST_NAME = ATT.PATIENTFIRSTNAME
AND M.TRM_RSN_CODE = 'A'
AND EXISTS
(SELECT 1 From dba1.member M
WHERE M.MBR_BRTH_DATE = ATT.PATIENTDOB
AND M.MBR_SEX_CODE = ATT.PATIENTSEX
AND M.MBR_SUB_ID = ATT.SUBSCRIBERID
AND M.MBR_FRST_NAME = ATT.PATIENTFIRSTNAME
AND M.TRM_RSN_CODE = 'A')
GROUP BY M.corp_mbr_id)
AS TEMP_TAB)

It is giving me a sqlstate 42703 (-206) error, I don't think it likes MBR_DEP_NBR in the outer Select statement.

Have fun, and thanks for your interest, it has help me to think about different ways to go about this and as a result I found my solution.

 

[ddiamond]

Lynchg,

Does tbl2.fldA have a unique value for each matching record in tbl1? I am assuming it does, because otherwise your original select from your first post could potentially return more than one record. Also I do not believe your exists clause serving any purpose. If your sub-queries where clause succeeds, then the exists clause will also succeed. Would the following query do what you want?

UPDATE tbl1 
SET (tbl1.fldA, tbl1.fldB) = (
  SELECT MIN(tbl2.fldA), MIN(tbl2.fldB)
  FROM tbl2
  WHERE tbl2.fldC = tbl1.fldC
    AND tbl2.fldD = tbl1.fldD
    AND tbl2.fldE = tbl1.fldE
    AND tbl2.fldF = tbl1.fldF);

Notice I did not need a group by clause because I am taking the min of both fields.
If you are concerned about the case where tbl1 does not match tbl2, you could add a where clause to your update:

UPDATE tbl1 
SET (tbl1.fldA, tbl1.fldB) = (
  SELECT MIN(tbl2.fldA), MIN(tbl2.fldB)
  FROM tbl2
  WHERE tbl2.fldC = tbl1.fldC
    AND tbl2.fldD = tbl1.fldD
    AND tbl2.fldE = tbl1.fldE
    AND tbl2.fldF = tbl1.fldF)
WHERE EXISTS (
  SELECT 1
  FROM tbl2
  WHERE tbl2.fldC = tbl1.fldC
    AND tbl2.fldD = tbl1.fldD
    AND tbl2.fldE = tbl1.fldE
    AND tbl2.fldF = tbl1.fldF);

 

[MarcLodge]

Lynchg,
The SQL you show in your last post doesn't work (I think) because you have used 'as DepNum' and then not used 'Depnum'.
What happens if you change it to:

UPDATE dba1.appt_temp_tbl 
SET (corp_mbr_id, MEMBERNUMBER) =
	(SELECT corp_mbr_id, DepNum FROM
		(SELECT M.corp_mbr_id, MIN(M.MBR_DEP_NBR) AS DepNum
		FROM dba1.member M
		WHERE M.MBR_BRTH_DATE = ATT.PATIENTDOB
		AND M.MBR_SEX_CODE = ATT.PATIENTSEX
		AND M.MBR_SUB_ID = ATT.SUBSCRIBERID 
		AND M.MBR_FRST_NAME = ATT.PATIENTFIRSTNAME 
		AND M.TRM_RSN_CODE = 'A'
		AND EXISTS 
			(SELECT 1 From dba1.member M
			WHERE M.MBR_BRTH_DATE = ATT.PATIENTDOB
			AND M.MBR_SEX_CODE = ATT.PATIENTSEX
			AND M.MBR_SUB_ID = ATT.SUBSCRIBERID 
			AND M.MBR_FRST_NAME = ATT.PATIENTFIRSTNAME 
			AND M.TRM_RSN_CODE = 'A')
		GROUP BY M.corp_mbr_id)
	AS TEMP_TAB)

Also, from the description I've just read of a -206, it seems to be objecting to a column in the UPDATE, so I'm not sure if this is a red herring. Are both the columns corp_mbr_id and MEMBERNUMBER in the appointments table, and is this their correct column names?

Marc

 

[lynchg]

ddiamond, tbl2.fld1 (the real table and field names are Member.corp_mbr_id), has a unique value for each matching record in tbl1(appt_temp_tbl), but there can be multiple records in the Member table with the same corp_mbr_id value.

This is a membership table for a health insurance company and the corp_mbr_id is a company wide unique identifier for a member. MBR_SUB_ID is a policy identifier, we keep history in our Member table so there can be multiple records per person with various MBR_SUB_ID values, but a common corp_mbr_id for all of that person's records.

ddiamond, you may have something with your SELECT MIN(tbl2.fldA), MIN(tbl2.fldB), I'll have to try it.

Marc, I see your point too about that field name. And yes, both the columns corp_mbr_id and MEMBERNUMBER are in appt_temp_tbl.

 

[lynchg]

DDiamond, your sql works.

Marc, I am getting the same (-815) error about a Group By in an embedded Select.

Thank you both for your input.

 

[MarcLodge]

Dan,

Another great idea. Well done.

Marc