UPDATE multiple tables

[code green]

The following query produces a very slow running failure. (Usually time out).
I am not sure about the syntax. Any ideas?

UPDATE `table1`,`table2`,`table3` SET [all fields in all three tables to values]
 WHERE `tables1`.`id` = '20439'

[Motoma]

The following query produces a very slow running failure. (Usually time out).
I am not sure about the syntax. Any ideas?

UPDATE `table1`,`table2`,`table3` SET [all fields in all three tables to values]
 WHERE `tables1`.`id` = '20439'

The EXPLAIN syntax should help you out here.
What is happening is that you are doing a 3 table join, without realizing it. You are updating tables 2 and 3 without any restrictions; all entries in these two tables are updates all the time.

In order to see what exactly you are updating, do a select on the same set:

SELECT * FROM  `table1`,`table2`,`table3`
WHERE `tables1`.`id` = '20439'

Another thing causing a problem is that you have a string as your ID. If that really is an integer in your table, you don't need the '' around it.

[code green]

Another thing causing a problem is that you have a string as your ID. If that really is an integer in your table, you don't need the '' around it.

Thanks. Didn't realise I had done that.

What is happening is that you are doing a 3 table join, without realizing it. You are updating tables 2 and 3 without any restrictions; all entries in these two tables are updates all the time.

Right so I need

UPDATE `table1`,`table2`,`table3` 
SET [all fields in all three tables to values]
 WHERE `tables1`.`id` = 20439 AND `tables2`.`id` = 20439 
AND `tables3`.`id` = 20439

So if the ID in one table does not exist the whole query will fail. I wanted to UPDATE the tables WHEREVER the ID appeared. This idea doesn't look possible without using IF EXISTS and sub queries checking for the ID in each table. What do you recommend?

[Motoma]

Thanks. Didn't realise I had done that.
Right so I need

UPDATE `table1`,`table2`,`table3` 
SET [all fields in all three tables to values]
 WHERE `tables1`.`id` = 20439 AND `tables2`.`id` = 20439 
AND `tables3`.`id` = 20439

So if the ID in one table does not exist the whole query will fail. I wanted to UPDATE the tables WHEREVER the ID appeared. This idea doesn't look possible without using IF EXISTS and sub queries checking for the ID in each table. What do you recommend?

You could use ORs in place of ANDs, but that will slower than hell. You should just use 3 queries.

[code green]

I've decided to use four. Three queries SELECT 1 FROM `table` WHERE `id` = 20439 for each of the three tables which should be fast and gives me the chance to catch any missing ids at this point. Then construct an UPDATE query live time as a php string using only the tables where the id was found. Thanks for your help Motoma.

[Motoma]

Glad I could help. Come back any time.

[gelmo214]

SIr Code green?

Can you share the code structure that you use in that 3 tables?..thanks ...

[gelmo214]

re: UPDATE multiple tables
I've decided to use four. Three queries SELECT 1 FROM `table` WHERE `id` = 20439 for each of the three tables which should be fast and gives me the chance to catch any missing ids at this point. Then construct an UPDATE query live time as a php string using only the tables where the id was found. Thanks for your help Motoma
>>>>>>>How can i apply those 3 select and UPDATE query in VB.net?thanks..