Update Table based upon different table

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)

Update Table based upon different table

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2006-02-06 : 21:19:44

New to SQL - trying to teach self - in reading the postings its obvious I am in the presence of greatness, pains me to ask simple question, but here goes:

Two Tables:
ItemMaster (Item Number, ClassID) and ItemQtyMaster (Item Number,MethodID)

Objective:
Update ItemQtyMaster based upon a condition exisiting in Item Master.

I want to update the Method column in ItemQtyMaster Table for each item number that has ClassId = X in ItemMaster Table

I tried:

IF EXISTS (select * from ITEMMASTER where ClassId = 'x')
Begin
Update set Method = 2 where ITEMMASTER.ClassId = 'x'
END

error message - The column prefix 'ITEMMASTER' does not match with a table name or alias name used in the query.

Do I need to create a join or can I modify my approach?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-06 : 21:26:24

Welcome to SQLTeam.

You can use Update command joining ItemMaster and ItemQtyMaster.

Update U
       set U.MethodID = 2
From   ItemQtyMaster U inner join ItemMaster M
       on U.ItemNumber = M.ItemNumber
Where  M.ClassId = 'x'

----------------------------------
'KH'

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-06 : 21:28:13

update ItemQtyMaster
set Method = 2
from ItemQtyMaster t
join ITEMMASTER t2
on t.[Item Number] = t2.[Item Number]
where t2.ClassId = 'x'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

btamulis
Yak Posting Veteran

64 Posts

Posted - 2006-02-06 : 21:49:29

Thanks to both - worked like a champ!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources