Update multiple rows by multi-valued data

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 2005 Forums

Transact-SQL (2005)

Update multiple rows by multi-valued data

Author

Topic

GetMaster
Starting Member

3 Posts

Posted - 2010-03-30 : 11:08:21

I want to update winner supplier filed based on least price they give. how can i write the query

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 11:11:57

You're going to need to give a little more information than that. Some sample data, and an idea of your table structure would be handy.

There are 10 types of people in the world, those that understand binary, and those that don't.

GetMaster
Starting Member

3 Posts

Posted - 2010-03-30 : 12:31:00

here is the code

UPDATE tblPriceComarison
SET ISWinner = '1' WHERE (PriceRequestNo = @PriceRequestNo AND Price IN (SELECT MIN(Price)
FROM dbo.tblProformaInvoice
WHERE (PriceRequestNo = @PriceRequestNo ) AND (UnitPrice > 0)
GROUP BY ItemCode, Unit)

I want to update a table's IsWinner field(Default 0) to 1 for a supplier who gives least price for a given PriceRequestNo that consist of Itemcode, unit.
the select statement filters those with least price. but the update does not effect on the table as required.Any help appriciated

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 12:39:54

Sample data and table structure

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:25:31

quote:
Originally posted by GetMaster

here is the code

UPDATE tblPriceComarison
SET ISWinner = '1' WHERE (PriceRequestNo = @PriceRequestNo AND Price IN (SELECT MIN(Price)
FROM dbo.tblProformaInvoice
WHERE (PriceRequestNo = @PriceRequestNo ) AND (UnitPrice > 0)
GROUP BY ItemCode, Unit)

I want to update a table's IsWinner field(Default 0) to 1 for a supplier who gives least price for a given PriceRequestNo that consist of Itemcode, unit.
the select statement filters those with least price. but the update does not effect on the table as required.Any help appriciated

what are related fields for two tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GetMaster
Starting Member

3 Posts

Posted - 2010-03-31 : 09:42:52

PriceRequestNo is primary key in its own table (tblPriceComparison) andfriegn key in the table tblProformaInvoice which has its own PK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:40:11

[code]
UPDATE pc
SET pc.ISWinner = '1'
FROM tblPriceComarison pc
CROSS APPLY(SELECT MIN(Price) AS MinPrice
FROM dbo.tblProformaInvoice
WHERE (PriceRequestNo = pc.PriceRequestNo )
AND (UnitPrice > 0))pi
WHERE pi.MinPrice = pc.Price
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources