CODEWITHSUNDEEP
phpmysql
Josh Kirkpatrick
Josh Kirkpatrick

Reputation: 403

MYSQL, multiple table update

i have a query that updates values in 6 tables,

the 6 tables are all in the same format with a structure like 

ID   Ref       Date
21    1     26/01/2015
22    2     26/01/2015
23    3     26/01/2015

And there is a table that holds a list of vehicles with a structure like, 

ID   Name       
1    name     
2    name     
3    name

So the Ref column in the 6 tables that need updating correspond with the ID of the vehicle name.

Here is the query i have at to moment, 

UPDATE transport_tax, transport_tachocalibration, transport_service, transport_rbt, transport_mot, transport_ivc
        SET transport_tax.Date = '$tax',
        transport_tachocalibration.Date = '$tacho',
        transport_service.Date = '$service',
        transport_rbt.Date = '$rbt',
        transport_mot.Date = '$mot',
        transport_ivc.Date = '$ivc',
        WHERE 
        transport_tax.Ref = '$id' AND
        transport_tachocalibration.Ref = '$id' AND
        transport_service.Ref = '$id' AND
        transport_rbt.Ref = '$id' AND
        transport_mot.Ref = '$id' AND
        transport_ivc.Ref = '$id'

this query in in PHP to that's why there are variables there, the 6 values that are to be updated too all equal 1/1/2015, The $id is equal to 1. Currently when this query is run i get this error 

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE transport_tax.Ref = '1' AND transport_tachocalibr' at line 8 in C:\wamp\www\application\models\Transport_model.php on line 76

Any help would be much appreciated,

Upvotes: 0

Views: 51

Answers (2)

alpham8
alpham8

Reputation: 1362

First of all remove comma in the line before WHERE and also make your query type safe, if you are running your database in strict mode. If you got integer or in general number values, you should not add the ticks, e. g.:

transport_tax.Ref = $id AND
...

Upvotes: 0

juergen d
juergen d

Reputation: 204766

Remove the comma before WHERE

transport_ivc.Date = '$ivc', WHERE
            here-----------^

Upvotes: 1

Related Questions