Page 1 of 1
UPDATE MULTIPLE TABLES in DATABASE
Posted: Tue Jun 15, 2004 3:07 pm
by poeta_eletrico
Hi !
I would like to know how to implement at the same code the condition to UPDATE two different tables at the same time, like:
update table1, table2 set email="test1", user_email="test1" where ....
(*) I was trying the example above but with no sucess.
I really appreciate any help comming from this.
Kind Regards,
Poeta_Eletrico
Posted: Tue Jun 15, 2004 3:42 pm
by xisle
mysql 4 will do it...
from the manual...
Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
Continuing
Posted: Tue Jun 15, 2004 3:54 pm
by poeta_eletrico
xisle wrote:mysql 4 will do it...
from the manual...
Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
Hi !
The situation is :
1 :
$query = "UPDATE users SET name = '" . $_POST['name'] . "', lastname = '" . $_POST['lastname'] . "', email ='" . $_POST['email'] "' WHERE user_id = " . $_POST['user_id'];
I have this running already with no problems. By now I need to the same time I UPDATE the table USERS need to UPDATE the table TIP because there I have a field called USER_EMAIL (sip) and in USERS i have a field called (EMAIL) ... so how this new feature in this mysql could acomplish to fit my needs? I saw this in the MYSQL.COM website but could not make up my mind to use it with my code. I am a newbie (of course).
But I think a problem that I have in the TIP table is the fact that I dont have a INDEX row to "talk" with USERS table as you described here:
(...) WHERE items.id=month.id; ...
What do you think? Is it possible to find a solution?
Poeta_Eletrico
Posted: Tue Jun 15, 2004 3:56 pm
by d3ad1ysp0rk
How do you tell which user email goes which whatever user from the other table then?
Posted: Wed Jun 16, 2004 7:29 am
by poeta_eletrico
LiLpunkSkateR wrote:How do you tell which user email goes which whatever user from the other table then?
Hi :
First of all i have an open session and then a form wich grab all the informations from a database. It s given to the user the condition, if it wants to, to change anything. but in this case, if this user changes the E-MAIL i must change only this field in two different tables one called - TIP and other called USER as described above.
I was imagining in creating an INDEX to these two tables so i could use the WHERE in the final end to have the EMAIL changed at the other table at the same time. My question is : HOW THIS SHOULD HAPPEN.... I hope I´ve bein more clearly now for you and thank you very much for your attention to my problem.
Poeta_Eletrico
Posted: Wed Jun 16, 2004 12:07 pm
by feyd
last I checked, it was bad database design to duplicate table data...
Posted: Wed Jun 16, 2004 1:47 pm
by lostboy
There should be a KEY to RELATE the RELATIONAL DATABASE TABLEs....strange how that should work....but Feyd's last post is correct. It is very bad to duplicate data...it will end up becoming unmanagable as the database grows and if there is no KEY to relate the tables, then you are screwed
Updating Multiple Tables
Posted: Thu Jun 17, 2004 7:44 am
by poeta_eletrico
lostboy wrote:There should be a KEY to RELATE the RELATIONAL DATABASE TABLEs....strange how that should work....but Feyd's last post is correct. It is very bad to duplicate data...it will end up becoming unmanagable as the database grows and if there is no KEY to relate the tables, then you are screwed
Hi :
I understand both point of view and I must agree with this. Unfortunatly today, I was not the one that have done this and until "second order" I will have to deal with it. But let s suppose the database is "good" how the code would become? I searched the web a lot but did not find something I could understand about UPDATING MULTIPLE TABLES at the same time.
Thank so much for your considerations.
Poeta_Eletrico
Posted: Thu Jun 17, 2004 9:28 am
by Weirdan
Code: Select all
update sometable inner join someothertable using(somecommonfield) set sometable.field1=someothertable.field2
And that is. If tables have no relation there's no sense to update them in one query because that would produce the update of full Cartesian product of the given tables (e.g. a LOT of rows). It would be much slower than 2 consequent queries.