Update using Group By and MIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update using Group By and MIN

Author

Topic

emurphy4
Starting Member

17 Posts

Posted - 2009-12-08 : 13:39:49

I need help change this select query into an Update query. I need to update a field named tran_type_cd to 'B4' on any record when the select statement is true.

SELECT MIN(mco_icn), script_id, days_supply_nbr, dispensed_qty_nbr, ncpdp_clm_desc, ndc_cd, provider_id, script_dt, service_dt
FROM dbo.ncpcp_pharmacy_encounters
Group by script_id, days_supply_nbr, dispensed_qty_nbr, ncpdp_clm_desc, ndc_cd, provider_id, script_dt, service_dt
having 1 < (COUNT(*))
order by script_id

Thanks for any help provided.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 13:48:53

What does this mean?

quote:
when the select statement is true

Can you maybe explain in words what you wud like to update and the condition for the update.

emurphy4
Starting Member

17 Posts

Posted - 2009-12-08 : 13:51:15

I think I have it, but need to see if anyone sees any issues with it.

UPDATE dbo.ncpcp_pharmacy_encounters
SET tran_type_cd = 'B4'
Where EXISTS(SELECT MIN(mco_icn), script_id, days_supply_nbr, dispensed_qty_nbr, ncpdp_clm_desc, ndc_cd, provider_id, script_dt, service_dt
FROM dbo.ncpcp_pharmacy_encounters
Group by script_id, days_supply_nbr, dispensed_qty_nbr, ncpdp_clm_desc, ndc_cd, provider_id, script_dt, service_dt
having 1 < (COUNT(*)))

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 13:56:17

This will update all the rows in the table with the value 'B4' for tran_type_cd if any row is returned from the SELECT.

Is that what you want?

emurphy4
Starting Member

17 Posts

Posted - 2009-12-08 : 14:05:13

Yes, any record that would be returned when the select statement is run, I would like to update with 'B4'.

quote:
Originally posted by vijayisonly

This will update all the rows in the table with the value 'B4' for tran_type_cd if any row is returned from the SELECT.

Is that what you want?

emurphy4
Starting Member

17 Posts

Posted - 2009-12-08 : 14:07:17

I just tested it and it updated all record with 'B4'.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 14:07:50

So..you're saying thats fine?

emurphy4
Starting Member

17 Posts

Posted - 2009-12-08 : 14:15:30

The select works fine, but when I put it in

UPDATE dbo.ncpcp_pharmacy_encounters
SET tran_type_cd = 'B4'
Where EXISTS("SELECT STATEMENT")

it updates all rows with 'B4', rather than just the rows I need from the select statement.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 14:19:42

Thats what I just said...

Try this..
(change the JOIN condition according to your data)

UPDATE a 
SET a.tran_type_cd = 'B4'
from dbo.ncpcp_pharmacy_encounters a
INNER JOIN
(
SELECT MIN(mco_icn) as mco_icn, script_id, days_supply_nbr, dispensed_qty_nbr, ncpdp_clm_desc, ndc_cd, provider_id, script_dt, service_dt
FROM dbo.ncpcp_pharmacy_encounters
Group by script_id, days_supply_nbr, dispensed_qty_nbr, ncpdp_clm_desc, ndc_cd, provider_id, script_dt, service_dt
having 1 < (COUNT(*)) 
) b
on a.script_id = b.script_id

If this doesn't work , post some sample data from the table.

emurphy4
Starting Member

17 Posts

Posted - 2009-12-08 : 15:15:51

Ok, thanks. I'll give it a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources