CODEWITHSUNDEEP
mysql
Sathish Kumar k k
Sathish Kumar k k

Reputation: 1842

update multi table simultaneously in MySQL

I have a queries like these below and I need it to be in a single update query. As I'm a beginner I use joins and wrote the single line query but I get error as

Lock wait timeout exceeded; try restarting transaction

Here below is the each Individual update query...

update a set id=id-1 where id>'3' and reg='34554';
update b set id=id-1 where id>'3' and reg='34554';
update c set id=id-1 where id>'3' and reg='34554';
update d set id=id-1 where id>'3' and reg='34554';
update e set id=id-1 where id>'3' and reg='34554';

Here below is what I tried out and got error as told above...

update a
LEFT JOIN b ON b.id=a.id and b.tan=a.tan
LEFT JOIN c ON c.id=b.id and c.tan=b.tan
LEFT JOIN d ON d.id=c.id and d.tan=c.tan
LEFT JOIN e ON e.id=d.id and e.tan=d.tan
SET a.id=b.id=c.id=d.id=e.id=a.id-1
where a.id>'3' and a.tan='34554';

Upvotes: 2

Views: 472

Answers (2)

user170851
user170851

Reputation: 405

You can store multiple sql in Stored procedures then call the procedure from java.. Id & reg can be passed thro parameters.

Info on stored procedures http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

You have to do this in Mysql command line...

Delimiter |

Create procedure (in id_val int, in reg_val int)

Begin

update a set id=id-1 where id>id_val and reg=reg_val;

update b set id=id-1 where id>id_val and reg=reg_val;

update c set id=id-1 where id>id_val and reg=reg_val;

update d set id=id-1 where id>id_val and reg=reg_val;

update e set id=id-1 where id>id_val and reg=reg_val;

End |

delimiter ;

I use PHP... info in following link http://php.net/manual/en/pdo.prepared-statements.php

Hope of java... this link may be useful http://www.easywayserver.com/jdbc/JDBC-prepared-statement.htm

Upvotes: 1

Jarosław Gomułka
Jarosław Gomułka

Reputation: 4995

You have to use transactions

SET autocommit=0;
LOCK TABLES a WRITE, b WRITE, c WRITE, d WRITE, e WRITE

update a set id=id-1 where id>'3' and reg='34554';
update b set id=id-1 where id>'3' and reg='34554';
update c set id=id-1 where id>'3' and reg='34554';
update d set id=id-1 where id>'3' and reg='34554';
update e set id=id-1 where id>'3' and reg='34554';

COMMIT;
UNLOCK TABLES;

You can read about combining lock tables with transactions here

I don't know, how to create such query with join.

Upvotes: 0

Related Questions