Update Query Error

[tmm]

When I run the following query, I get the response of subquery evaulated to more than one row. mbr_sys_id = XXXX fst_srvc_dt = xxxx

proc sql;

update cardiac.pos2 a

   set pl_of_srvc_sys_id = (select pl_of_srvc_sys_id from cardiac.pos b

where a.mbr_sys_id=b.mbr_sys_id and a.fst_srvc_dt = b.fst_srvc_dt),

pl_of_srvc_sum_desc = (select pl_of_srvc_sum_desc from cardiac.pos b

where a.mbr_sys_id=b.mbr_sys_id and a.fst_srvc_dt = b.fst_srvc_dt)

where a.pl_of_srvc_sys_id is null and pl_of_srvc_sum_desc is null;

run;

[DBailey]

Have you checked to see if the query

select * from cardiac.pos b where mbr_sys_id=XXX and fst_srvc_dt=xxx

returns multiple rows?

[tmm]

Yes it does have multiple rows. I cannot change the multiple rows within the table being updated because it has to be as it is.

[DBailey]

How would you determine which of the multiple rows to use for the update?

[tmm]

Nevermind. I changed it to the following because the reason for multiple member ids is we have various provider tins and mpins that serviced the member. So, I had to pull in a table that says give me member, tin, mpin place of service and service desc. so i did it this way instead with the update

proc sql;

update cardiac.pos2 a

   set pl_of_srvc_sys_id = (select pl_of_srvc_sys_id from cardiac.pos b

where a.mbr_sys_id=b.mbr_sys_id and a.fst_srvc_dt = b.fst_srvc_dt and a.prov_tin = b.prov_tin and a.mpin = b.mpin),

pl_of_srvc_sum_desc = (select pl_of_srvc_sum_desc from cardiac.pos b

where a.mbr_sys_id=b.mbr_sys_id and a.fst_srvc_dt = b.fst_srvc_dt and a.prov_tin = b.prov_tin and a.mpin = b.mpin)

where a.pl_of_srvc_sys_id is null and pl_of_srvc_sum_desc is null;

run;

It is running now. No row error. The only problem is I changed it yesterday about 4pm and it is now 11am the next day and still running. But, it has to go through 10.1 million rows sooooooooooo Will let you know if this works in the end and if so, great. Thanks.

[DBailey]

lots of columns/records to update....

Is cardiac.pos sorted or indexed?  If not, then you definitely need to do that.