Why does this work? - 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 2005 Forums

Transact-SQL (2005)

Why does this work?

Author

Topic

underwms
Starting Member

2 Posts

Posted - 2007-09-13 : 11:26:43

Can anybody tell me why this works?
UPDATE pod
SET pod.warehouse_id = pom.warehouse_id
FROM #temp_po_detail AS pod (NOLOCK)
JOIN
#temp_po_master AS pom (NOLOCK)
ON pom.po_number = pod.po_number

And this doesn't
UPDATE #temp_po_detail
SET warehouse_id = (SELECT TOP 1 pom.warehosue_id)
FROM #temp_po_detail AS pod (NOLOCK)
JOIN
#temp_po_master AS pom (NOLOCK)
ON pom.po_number = pod.po_number)

Some background: this is for a warehouse inventory system; inside pom is the master data about the PO and pod has the details such as line items.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-13 : 11:30:29

What error do you get? Did you mis-type: SELECT TOP 1 pom.warehosue_id?

Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:31:30

[code]UPDATE pod
SET pod.warehouse_id = pom.warehouse_id
FROM #temp_po_detail AS pod (NOLOCK)
inner JOIN #temp_po_master AS pom (NOLOCK) ON pom.po_number = pod.po_number

UPDATE #temp_po_detail
SET warehouse_id = (SELECT TOP 1 pom.warehosue_id) -- here is wrong syntax
FROM #temp_po_detail AS pod (NOLOCK)
/* This part is not needed since yuo are using a correlated subquery above
JOIN
#temp_po_master AS pom (NOLOCK)
ON pom.po_number = pod.po_number)
*/

This would be the correcte syntax for second update statement

UPDATE #temp_po_detail
SET warehouse_id = (SELECT TOP 1 pom.warehosue_id FROM #temp_po_detail AS pod (NOLOCK) WHERE #temp_po_detail.po_number = pod.po_number)[/code]

E 12°55'05.25"
N 56°04'39.16"

underwms
Starting Member

2 Posts

Posted - 2007-09-13 : 11:35:15

sorry my syntax is off here it is proper

Why does this work?
UPDATE pod
SET pod.warehouse_id = pom.warehouse_id
FROM #temp_po_detail AS pod (NOLOCK)
JOIN
#temp_po_master AS pom (NOLOCK)
ON pod.po_number = pom.po_number

And this doesn't
UPDATE #temp_po_detail
SET warehouse_id = (SELECT TOP 1 pom.warehouse_id
FROM #temp_po_detail AS pod
JOIN
#temp_po_master AS pom
ON pod.po_number = pom.po_number
WHERE pod.po_number = pom.po_number)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:49:07

The second query do not need an extra join. The query parser already knows which record it works with.
Please feel free to ignore my previous explanation...

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources