MYSQL, multiple table update

Question

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,

remove the comma vc.Date = '$ivc',

ɹɐqʞɐ zoɹǝɟ
– ɹɐqʞɐ zoɹǝɟ

2015-01-26 12:03:56 +00:00
Commented Jan 26, 2015 at 12:03 — ɹɐqʞɐ zoɹǝɟ
– ɹɐqʞɐ zoɹǝɟ, Commented Jan 26, 2015 at 12:03

juergen d · Accepted Answer · 2015-01-26 12:02:11Z

1

Remove the comma before WHERE

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

answered Jan 26, 2015 at 12:02

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh Kirkpatrick Over a year ago

Wow i feel like a complete idiot, i was looking of this for ages can't believe i missed that :/ too tired, Thank you

alpham8 · Accepted Answer · 2015-01-26 12:07:53Z

0

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
...

answered Jan 26, 2015 at 12:07

alpham8

1,3722 gold badges15 silver badges32 bronze badges

Collectives™ on Stack Overflow

MYSQL, multiple table update

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related