help to update data from more then one 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

General SQL Server Forums

New to SQL Server Programming

help to update data from more then one table

Author

Topic

SHIVPREET2K1
Starting Member

32 Posts

Posted - 2009-06-10 : 01:14:31

Dear friends

I have three tables which are given below


Material_family     Raw_Material             RM_Costing
Mat_Category (PK)      Mat_Category             Item_Code(PK) 
Scrap_Rate             RM_Code (PK)             RM_Code (PK)
                                                Scrap_Wt
                                                Scrap_tot
                             



in rm costing table item_code and rm_code make a composite key

i want to upate the scrap_tot field in rm_Costing as

scrap_tot=rm_Costing.scrap_Wt*MAterial_family.scrap_Rate


my query is given below

UPDATE rm_Costing SET scrap_total=(SELECT     
(rm_Costing.Scrap_wt* Material_Family.Scrap_Rate) as scrap_total_Value
FROM
Raw_Material ,rm_Costing,Material_Family

where 
Raw_Material.rM_Code = rm_Costing.RM_Code

and

Raw_Material.MAt_Category = Material_Family.MAt_Category
)

But Each time when i run this query, i get the error of more then one row returned. please help me to correct it.

thanks in advance

shivpreet2k1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-10 : 01:22:16

try like this
UPDATE rc
SET scrap_total=rc.Scrap_wt* mf.Scrap_Rate
FROM rm_Costing rc
INNER JOIN Raw_Material rm ON rm.rM_Code = rc.RM_Code
INNER JOIN Material_Family mf ON rm.MAt_Category = mf.MAt_Category