help with updating derrived table

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

SQL Server 2000 Forums

SQL Server Development (2000)

help with updating derrived table

Author

Topic

dpurcell
Starting Member

2 Posts

Posted - 2008-11-18 : 13:07:11

For simplicity, assume I have a table about pears and their descriptions. Some pears have multiple descriptions, each having their own record. Like this:

PEARID - DESCRIPTION
Pear01 - bruised
Pear01 - green
Pear02 - green
Pear03 - funny looking

I need to find only the bruised pears and update all records for those so each record identifies it as bruised. From above, Pear01 - green would become Pear01 - bruised. There would then be two records in the database that said Pear01 - bruised.

Also assume I want to leave the original descriptions alone and put my update into another column called NewDescription like below.

PEARID - DESCRIPTION - NEWDESCRIPTION
Pear01 - bruised - bruised
Pear01 - green - bruised
Pear02 - green - green
Pear03 - funny looking - funny looking

Currently I can identify the records I want using the following derived table, but I'd like to reduce steps and make this derrived table update on the fly:

select Pears_table.PearID, BRUISEDPEARS.COUNTOFBRUISED
from Pears_table 
left join 
(select PearID, count(*) as COUNTOFBRUISED
from Pears_table
where description like ‘bruised'
group by PearID) as HFC
on
         Pears_table.PearID = BRUISEDPEARS.PearID
group by Pears_table.PearID,  BRUISEDPEARS.COUNTOFBRUISED
having COUNTOFBRUISED > 0
order by Pears_table.PearID

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 13:53:29

why group by twice?


select Pears_table.PearID, Pears_table.DESCRIPTION,
CASE WHEN HFC.PearID IS NOT NULL THEN 'bruised' ELSE Pears_table.DESCRIPTION END AS NEWDESCRIPTION   
from Pears_table 
left join 
(select PearID
from Pears_table
group by PearID
HAVING SUM(CASE WHEN DESCRIPTION ='bruised' THEN 1 ELSE 0 END)>0
) as HFC
on         Pears_table.PearID = HFC.PearID

dpurcell
Starting Member

2 Posts

Posted - 2008-11-18 : 14:05:28

Thank you. I was able to edit that to run the update query in one shot.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources