By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

Update using a join in the where clause

P: 4
I am trying to run this update qry in SQL Server:

UPDATE [mfgtest803].[dbo].[partplant]
SET [reschedoutdelta] = '1'

WHERE ([partplant].[partnum] = [partWhse].[partnum])AND
(([partplant].[safetyqty]>'0')OR([partplant].[minimumqty]>'0'))AND
([partWhse].[WarehouseCode]='120')


I get the following errors:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "partWhse.partnum" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "partWhse.WarehouseCode" could not be bound.

But this select statement works fine:

SELECT [partplant].[plant]
,[partplant].[partnum]
,[partplant].[primwhse]
,[partplant].[minimumqty]
,[partplant].[maximumqty]
,[partplant].[safetyqty]

,[partplant].[reschedoutdelta]
,[partplant].[reschedindelta]
,[partplant].[nonstock]
,[partWhse].[WarehouseCode]

FROM [mfgtest803].[dbo].[partplant], [mfgtest803].[dbo].[partWhse]

WHERE ([partplant].[partnum] = [partWhse].[partnum])AND
(([partplant].[safetyqty]>'0')OR([partplant].[minimumqty]>'0'))AND
([partWhse].[WarehouseCode]='120')


Can someone help me understand what is wrong and how to fix?

thanks!
Feb 1 '10 #1

✓ answered by code green

This is a different problem altogether.
if a partplant record has corosponding partwhse records equal to 120 but not 320,
By this you mean UPDATE if partwhse has a corresponding value of 120 ONLY.
If this is so then (excuse me while I remove all the brackets, are you using Access?)
Expand|Select|Wrap|Line Numbers
  1. WHERE (partplant.safetyqty > 0 
  2.     OR partplant.minimumqty > 0) AND
  3. (partWhse.WarehouseCode = 120 
  4.     AND COUNT(partWhse.WarehouseCode) = 1)
This is assuming your JOIN is correct.
If more than two entries are allowed (multiple 120 or other values) then a sub-query will probably be needed
Expand|Select|Wrap|Line Numbers
  1. WHERE ..id.. NOT IN 
  2. (SELECT ..id.. FROM partWhse 
  3.     WHERE partWhse.WarehouseCode = 320)
..or similar

Share this Question
Share on Google+
5 Replies


code green
Expert 100+
P: 1,726
Please remove the Bold My eyes are hurting.
You haven't done a JOIN on partWhse that is why the error states 'cannot be found'
Feb 2 '10 #2

P: 4
thanks CG! That was it. (sorry about the bolding)

I now have this q:

UPDATE [mfgtest803].[dbo].[partplant]
SET [reschedindelta] = '15'
FROM [mfgtest803].[dbo].[partplant] JOIN [mfgtest803].[dbo].[partWhse] ON ([partplant].[partnum]=[partWhse].[partnum])
WHERE (([partplant].[safetyqty]>'0')OR([partplant].[minimumqty]>'0'))AND
(([partWhse].[WarehouseCode]='120') AND([partWhse].[WarehouseCode]<>'320'))

But there is a problem --> a partplant record often has 2 corosponding partwhse records, whose values could be 120 and 320. IF this is the case, I do not want to update (i.e., if a partplant record has corosponding partwhse records equal to 120 but not 320, then do the update). The above query updates any partplant record that has a corosponding partwhse record = 120. Am I making sense? How can I stop this?

thanks for your help CG
Feb 2 '10 #3

code green
Expert 100+
P: 1,726
This is a different problem altogether.
if a partplant record has corosponding partwhse records equal to 120 but not 320,
By this you mean UPDATE if partwhse has a corresponding value of 120 ONLY.
If this is so then (excuse me while I remove all the brackets, are you using Access?)
Expand|Select|Wrap|Line Numbers
  1. WHERE (partplant.safetyqty > 0 
  2.     OR partplant.minimumqty > 0) AND
  3. (partWhse.WarehouseCode = 120 
  4.     AND COUNT(partWhse.WarehouseCode) = 1)
This is assuming your JOIN is correct.
If more than two entries are allowed (multiple 120 or other values) then a sub-query will probably be needed
Expand|Select|Wrap|Line Numbers
  1. WHERE ..id.. NOT IN 
  2. (SELECT ..id.. FROM partWhse 
  3.     WHERE partWhse.WarehouseCode = 320)
..or similar
Feb 2 '10 #4

P: 4
got it - thanks. I will give that a try. I will need the sub-q.
Feb 2 '10 #5

P: 4
Thanks for your help! this did the trick:

UPDATE [mfgtest803].[dbo].[partplant]
SET [reschedindelta] = '15'
FROM [mfgtest803].[dbo].[partplant] JOIN [mfgtest803].[dbo].[partWhse] ON ([partplant].[partnum]=[partWhse].[partnum])
WHERE (([partplant].[partnum]
NOT IN
(SELECT [partWhse].[partnum] FROM [mfgtest803].[dbo].[partWhse] WHERE [partWhse].[WarehouseCode]<>'120'))
AND
(([partplant].[safetyqty]>'0')OR([partplant].[minimumqty]>'0')))

BTW, I am running this via SQL Management Studio Express - it does not run without the brackets...

Thanks Again!
Feb 2 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.