November 4, 2004 at 10:34 am
Hello all,
I would like to update records in a table based on a value from another table. I believe I have the logic down but not the syntax.
Here is a brief description of the tables.
Table: Vendor with Fields AT, Name.
Table: Product with Fields Type, Subtype.
Table:Equipment with fields IND, Type, Subtype
Table: Warranty with fileds IND, Sales, Support
The table that needs to be updated is the Warranty Table. I need to update the Sales and Support fields with the correct AT value in the Vendor table. Now the only correlation between the two tables involves having to look at the Equipment table first, then the Product table. For instance the vendor table would look like this.
Vendor
AT Name
0 Null
1 Cisco
2 Nortel
The warranty table would look like this.
Warranty
IND Sales Support
511 0 0
960 0 0
The product table would look like this
Product
Type Subtype Name
1200 15 Cisco
2000 18 Nortel
The Equipment Table would look like this
IND Type Subtype
511 1200 15
960 2000 18
So what I need to do is have the Warranty table's fields Sales, Support updated from 0 to the value of the correct vendor, as defined in the vendor table. The problem is I have to match the name fields in the Product table, then put that name value into some variable, then match the type and subtype value in the Product table to the type and subtype values in the Equipment table. Then I need the IND value in the equipment table which would give me the correct value for the record I need to update in the warranty table.
So the most basic way to perform this update is with a statement like.
update warranty set sales = 1 where IND = 511;
However I am dealing with about 10,000 records. Can anyone assist me with some type of script on how to do this. Your help is GREATLY appreciated.
Keith
November 4, 2004 at 10:20 pm
It's Simple.
You just need the following query to update all your Support and Sales fields.
update warranty
set Sales = vendor.[AT]
,Support = vendor.[AT]
from warranty join Equipment
on warranty.IND = Equipment.IND
join product
on Equipment.SubType = product.SubType
join Vendor
on product.Name = Vendor.Name
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply