February 5, 2009 at 1:25 pm
Hi. I have been asked to SET a value in a table WHERE the Product has > 2 Suppliers.
I have created a script to create the table in tempDB + INSERT 2000 rows of test data.
The field to update is 'SupplierSequence'. By default they are all set to '999'. Where a Product has more than 2 suppliers the Product with the latest 'DateLastQuoted' then the 'SupplierSequence' should be set to '500'.
However I think some Products have more than 2 suppliers + the DateLastQuoted are the same (do not know how to handle those, might not be that many).
Can anyone help on this?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 5, 2009 at 2:48 pm
Please try (but if values in DateLastQuoted are the same it will update all records with the DateLastQuoted):
UPDATE dbo.Forum SET SupplierSequence = '500'
FROM dbo.Forum f1
INNER JOIN (SELECT Product, MAX(DateLastQuoted) DateLastQuoted
FROM dbo.Forum
GROUP BY Product
HAVING COUNT(1) > 1) f2
ON f1.Product = f2.Product
AND f1.DateLastQuoted = f2.DateLastQuoted
February 5, 2009 at 3:14 pm
Thanks NULL. Excuse the error in my script (lack of INSERT INTO......I deleted in error!).
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 5, 2009 at 3:34 pm
Just a thought......after the update is there a way to filter WHERE Products appear more than once + WHERE the DateLatQuoted match AND SequenceNumber = 500.
On the 'Live' data 3000 records were updated.
There should not be that many, I can then decide which value to reverse back to 999 manually.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 6, 2009 at 8:43 am
Try:
SELECT dbo.forum.Product
,dbo.forum.Supplier
,dbo.forum.SupplierCrossReference
,dbo.forum.DateLastQuoted
FROM dbo.forum
WHERE SupplierSequence = '500'
AND Product IN (SELECT Product
FROM dbo.Forum
WHERE SupplierSequence = '500'
GROUP BY Product
HAVING COUNT(1) > 1)
or
SELECT dbo.forum.Product
,dbo.forum.Supplier
,dbo.forum.SupplierCrossReference
,dbo.forum.DateLastQuoted
FROM dbo.forum
INNER JOIN (SELECT Product
,MAX(DateLastQuoted) DateLastQuoted
FROM dbo.Forum
WHERE SupplierSequence = '500'
GROUP BY Product
HAVING COUNT(1) > 1) p
ON p.Product = dbo.forum.Product
AND p.DateLastQuoted = dbo.forum.DateLastQuoted
WHERE dbo.forum.SupplierSequence = '500'
February 6, 2009 at 9:13 am
I think the OP wants to update products where suppliers for that product > 2. So, should the rows be filtered by COUNT(1) > 2?
--Ramesh
February 6, 2009 at 9:19 am
Yes, Ramesh is right, it should be > 2
February 6, 2009 at 12:42 pm
Thanks Guys.
HAVING COUNT(1) > 1 appears to be correct (returned 106 rows), HAVING COUNT(1) > 2 returned 0.
Great Work,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply