Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SET value WHERE Product has > 2 Suppliers Expand / Collapse
Author
Message
Posted Thursday, February 5, 2009 1:25 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
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


  Post Attachments 
Query.txt (7 views, 131.33 KB)
Post #651222
Posted Thursday, February 5, 2009 2:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 28, 2014 10:58 AM
Points: 337, Visits: 360
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

Post #651297
Posted Thursday, February 5, 2009 3:14 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
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
Post #651317
Posted Thursday, February 5, 2009 3:34 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
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
Post #651327
Posted Friday, February 6, 2009 8:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 28, 2014 10:58 AM
Points: 337, Visits: 360
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'

Post #651748
Posted Friday, February 6, 2009 9:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,551, Visits: 2,593
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

Post #651781
Posted Friday, February 6, 2009 9:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 28, 2014 10:58 AM
Points: 337, Visits: 360
Yes, Ramesh is right, it should be > 2
Post #651786
Posted Friday, February 6, 2009 12:42 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:01 AM
Points: 625, Visits: 1,084
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
Post #651934
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse