The multi-part identifier "Inventory.InventoryId"

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

General SQL Server Forums

New to SQL Server Programming

The multi-part identifier "Inventory.InventoryId"

Author

Topic

kylemackey
Starting Member

3 Posts

Posted - 2009-01-04 : 21:56:20

I am getting the error: "The multi-part identifier "Inventory.InventoryId" could not be bound." from the following code:

-- Run frequently set WarehouseLocation equal to Storage Location if StorageLocation is blank
UPDATE Inventory
SET StorageLocation = ( SELECT WarehouseLocation from Items WHERE Items.InventoryId = Inventory.InventoryId )
WHERE StorageLocation != ( SELECT WarehouseLocation from Items WHERE Items.InventoryId = Inventory.InventoryId )
and ( SELECT WarehouseLocation from Items WHERE Items.InventoryId = Inventory.InventoryId ) != ''
and StorageLocation = ''

BUT, the following inverse code works without error:

-- Run frequently set WarehouseLocation equal to Storage Location if WarehouseLocation is blank
UPDATE Items
SET WarehouseLocation = ( SELECT StorageLocation from Inventory WHERE Inventory.InventoryId = Items.InventoryId )
WHERE WarehouseLocation != ( SELECT StorageLocation from Inventory WHERE Inventory.InventoryId = Items.InventoryId )
and ( SELECT StorageLocation from Inventory WHERE Inventory.InventoryId = Items.InventoryId ) != ''
and WarehouseLocation = ''

Any help would be appreciated... I am very new to SQL so please spell it out for me. Thanks in advance!

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-04 : 23:16:08

You did not use(join) the table inventory in the first UPDATE Statement so its not possible to use the column InventoryId

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-04 : 23:17:00

You can join with items table in the select statement

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-04 : 23:42:51

The query should be using a join, not subqueries:

UPDATE it
SET WarehouseLocation = inv.StorageLocation
FROM Items it
INNER JOIN Inventory inv
it.InventoryId = inv.InventoryId
WHERE ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kylemackey
Starting Member

3 Posts

Posted - 2009-01-05 : 19:56:11

Tried:
UPDATE inv
SET StorageLocation = it.WarehouseLocation
FROM Inventory inv
INNER JOIN Items it
inv.InventoryID = it.InventoryID
WHERE StorageLocation != it.WarehouseLocation
and it.WarehouseLocation != ''
and inv.InventoryID = 3051
and StorageLocation = ''

I am getting: Incorrect syntax near 'inv'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-05 : 19:58:19

I missed the ON word:

...
INNER JOIN ...
ON it.InventoryId = inv.InventoryId
...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kylemackey
Starting Member

3 Posts

Posted - 2009-01-05 : 23:10:11

Working Great... thanks soooooo much!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 00:02:58

You're welcome.