Update using a join in the where clause

[NightZEN]

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!

[code green]

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'

[NightZEN]

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

[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?)

WHERE (partplant.safetyqty > 0 
    OR partplant.minimumqty > 0) AND
(partWhse.WarehouseCode = 120 
    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

WHERE ..id.. NOT IN 
(SELECT ..id.. FROM partWhse 
    WHERE partWhse.WarehouseCode = 320)

..or similar

[NightZEN]

got it - thanks. I will give that a try. I will need the sub-q.

[NightZEN]

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!