Update new column/tbl1 with data from column/tbl2

[swtrader]

Given:

3 tables:
(1) customer (2) contract (3) fleet

1-to-many relationship customer-to-contract and contract-to-fleet

The fleet table lists vehicles with fields: VehID (pk), contract_no (fk), mfg, VIN, and a just-added field plateno

The customer has sent in a spreadsheet (imported into table: temp1) that has the plate number for each vehicle. I am trying to update the fleet table with the new plate number.

I have tried the following:

UPDATE tblVehicles SET tblVehicles.plateno = (SELECT plate
FROM temp1
WHERE tblVehicles.VIN = temp1.VIN)
WHERE EXISTS
(SELECT temp1.plate
FROM temp1
WHERE tblVehicles.VIN = temp1.VIN);

but get an error message "...must be updateable table."

Ideas??

Thanks.

dr

 

[lespaul]

I think it has to be like:

UPDATE tblVehicles v INNER JOIN temp1 t on v.VIN = t.VIN SET v.PlateNo = t.Plate

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[swtrader]

I haven't yet run yours, Leslie, but I did find this elsewhere and it worked great. As usual, I was trying to make it too difficult.

UPDATE tblVehicles, temp1
SET tblVehicles.plateno = temp1.plate
WHERE tblVehicles.VIN = temp1.VIN;

(I didn't think about the UPDATE being on both tables since I am not really updating temp1...but it does have to be 'called up' as part of the query.)

Thanks, all.

 

[lespaul]

basically doing the same thing. By adding both tables to your FROM clause, a cartesian product (type of join) is created, you then just need to qualify with the WHERE clause. By adding the INNER JOIN you also create a type of JOIN but since it's specific to the VIN, you don't need the WHERE clause.

Glad you got it sorted out!

les

 

[willir]

PHV posted a great link that explains the reason why you have to use a JOIN instead of a WHERE
Harnessing the Power of Updatable Queries

 

[swtrader]

Les & Willir/

Good advice. Thanks for the tip on the article, too, Willir.

dr