By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,621 Members | 1,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,621 IT Pros & Developers. It's quick & easy.

UPDATE multiple tables

code green
Expert 100+
P: 1,726
The following query produces a very slow running failure. (Usually time out).
I am not sure about the syntax. Any ideas?
Expand|Select|Wrap|Line Numbers
  1. UPDATE `table1`,`table2`,`table3` SET [all fields in all three tables to values]
  2.  WHERE `tables1`.`id` = '20439'
Apr 4 '07 #1
Share this Question
Share on Google+
7 Replies

Motoma
Expert 2.5K+
P: 3,237
The following query produces a very slow running failure. (Usually time out).
I am not sure about the syntax. Any ideas?
Expand|Select|Wrap|Line Numbers
  1. UPDATE `table1`,`table2`,`table3` SET [all fields in all three tables to values]
  2.  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:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM  `table1`,`table2`,`table3`
  2. WHERE `tables1`.`id` = '20439' 
  3.  
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.
Apr 6 '07 #2

code green
Expert 100+
P: 1,726
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
Expand|Select|Wrap|Line Numbers
  1. UPDATE `table1`,`table2`,`table3` 
  2. SET [all fields in all three tables to values]
  3.  WHERE `tables1`.`id` = 20439 AND `tables2`.`id` = 20439 
  4. 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?
Apr 10 '07 #3

Motoma
Expert 2.5K+
P: 3,237
Thanks. Didn't realise I had done that.
Right so I need
Expand|Select|Wrap|Line Numbers
  1. UPDATE `table1`,`table2`,`table3` 
  2. SET [all fields in all three tables to values]
  3.  WHERE `tables1`.`id` = 20439 AND `tables2`.`id` = 20439 
  4. 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.
Apr 10 '07 #4

code green
Expert 100+
P: 1,726
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.
Apr 10 '07 #5

Motoma
Expert 2.5K+
P: 3,237
Glad I could help. Come back any time.
Apr 10 '07 #6

P: 6
Can you share the code structure that you use in that 3 tables?..thanks...
Jul 5 '10 #7

P: 6
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..
Jul 5 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.