Update Records from a second table

[jtarry]

I have tables containing

supppart

supp spart sqty
0100 AAA 0
0200 AAA 0
0300 AAA 0
0100 BBB 0
0200 CCC 0

order

opart oqty
AAA 1
BBB 1
CCC 1

I wish to update first table to show

supp spart sqty
0100 AAA 1
0200 AAA 1
0200 AAA 1
0100 BBB 1
0200 CCC 1

If only one part record in first table I can update, mulitple entries I cant.

script I use is

update suppart
set sqty = (select sqty from suppart where spart = opart)
where spart = (select spart from suppart where spart = opart)

result I get

supp spart sqty
0100 AAA 0
0200 AAA 0
0200 AAA 0
0100 BBB 1
0200 CCC 1

Thanks

 

[r937]

update suppart
   set sqty = t2.oqty
  from suppart t1
inner
  join [order] t2
    on t1.spart = t2.opart

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jtarry]

Thanks