Updating columns within a group where any line meets a criteria...

[Jeniferous]

I have a table which contains details of stock items. This table might have 7 rows for the same product within different warehouses. The business process states that when a user marks a single warehouse product combo as obsolete (by adding OBS to the start of the description field) that they should do the same for all the warehouses containing that product, i.e. all seven lines. Of course this doesn't happen so now I need to copy the description from any OBS lines to the "non" OBS lines...

This is what i have...

update dbo.obsstockm set long_description =
(Select long_description from dbo.obsstockm where long_description LIKE 'OBS%' and dbo.obsstockm.product = dbo.obsstockm.product)
where dbo.obsstockm.product = dbo.obsstockm.product


but this gives me an error because it finds more that one OBS record in the sub query. If i put a top 1 in next to the select statement then it just picks the first OBS it finds in the table rather than for each group of product codes?

Help!

 

[drlex]

You could use a MAX function in conjunction with a grouping by product to return just one value per product.
i.e.

UPDATE dbo.obsstockm 
SET long_description = new_description 
FROM dbo.obsstockm.product 
INNER JOIN(Select product, MAX(long_description) as new_description from dbo.obsstockm where long_description LIKE 'OBS%' group by product) T1 on
dbo.obsstockm.product = T1 product

soi là, soi carré