Learning UPDATE query. - SQL Server Forums

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

Transact-SQL (2000)

Learning UPDATE query.

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-28 : 23:30:47

This are my datas...

tblStocks
StockCode -Item
-----------------------------
S2004J8001 -Burger
S2004JM000 -Siopao

tblStockQueue
qQueueCode -qPriority -qStockCode -qPrice
--------------------------------------------------------
Q2004JK000 -1 -S2004J8001 -2.00
Q2004JM000 -1 -S2004JM000 -9.00
Q2004JR000 -2 -S2004J8001 -3.00

tblStockLocal
siStockCode -siQueueCode -siPrice -siItem
-------------------------------------------------------
S2004J8001 -Q2004JK111 -1.00 -Kikiam

Now i want to update tblStockLocal and set siQueueCode, siPrice, siItem
based on the MINiMUM value of qPriority by qStockCode = 'S2004J8001' specified.

Result...
siStockCode -siQueueCode -siPrice -siItem
-------------------------------------------------------
S2004J8001 -Q2004JK000 -2.00 -Burger

update using left join i think?

Want Philippines to become 1st World COuntry? Go for World War 3...

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-29 : 03:08:16

inner join inline-view with top 1 order by

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-29 : 03:14:36

hgorijal pls post how?

Want Philippines to become 1st World COuntry? Go for World War 3...

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-29 : 05:04:36

update A set siQueueCode = B.qQueueCode, siPrice = B.qPrice, siItem = C.Item
from tblStockLocal A,
(select top 1 * from tblStockQueue where qStockCode = 'S2004J8001' order by qPriority) B
,tblStocks C
where A.siStockCode = B.qStockCode
and A.siStockCode = C.StockCode
and A.siStockCode = 'S2004J8001'

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources