UPDATE MULTIPLE TABLES in DATABASE

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
poeta_eletrico
Forum Commoner
Posts: 32
Joined: Mon Dec 22, 2003 7:33 am
Contact:

UPDATE MULTIPLE TABLES in DATABASE

Post 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
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post 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;
poeta_eletrico
Forum Commoner
Posts: 32
Joined: Mon Dec 22, 2003 7:33 am
Contact:

Continuing

Post 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
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

How do you tell which user email goes which whatever user from the other table then?
poeta_eletrico
Forum Commoner
Posts: 32
Joined: Mon Dec 22, 2003 7:33 am
Contact:

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

last I checked, it was bad database design to duplicate table data...
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
poeta_eletrico
Forum Commoner
Posts: 32
Joined: Mon Dec 22, 2003 7:33 am
Contact:

Updating Multiple Tables

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

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