CODEWITHSUNDEEP
mysqldatabase
stockBoi
stockBoi

Reputation: 287

How to update multiple tables by single mysql query?

I have two tables tb1 & tb2 I have to update a common column of both tables, i.e user_level I have a common criteria for both tables like username.

So I want to update like this:

UPDATE tb1, tb2 SET user_level=1 WHERE username="Mr.X"

But somehow it is not working. What would be the correct mysql query for this?

Upvotes: 3

Views: 31552

Answers (5)

Asuquo12
Asuquo12

Reputation: 835

The correct query is that you have to specify the full table and row/column you are trying to update in the two tables and indeed database if you are updating across databases too.

typical example:

UPDATE tb1, tb2 SET tb1.username = '$username', tb2.location ='New York'WHERE tb1.userid = '$id' AND tb2.logid = '$logid'

Upvotes: 1

Premjith
Premjith

Reputation: 163

We can update it without join like this

UPDATE table1 T1, table2 T2 
SET T1.status = 1 ,T2.status = 1 
WHERE T1.id = T2.tab1_id and T1.id = 1

We can update it with join like this

UPDATE table1 
INNER join table2 on table1.id=table2.tab1_id 
SET table1.status=3,table2.status=3
WHERE table1.id=1

Upvotes: 0

Hiral Pandya
Hiral Pandya

Reputation: 61

Try this:

UPDATE table_1 tb1, 
       table_2 tb2, 
       table_3 tb3 
SET    tb1.field2 = "some value", 
       tb2.field3 = "some other value", 
       tb3.field4 = "some another value" 
WHERE  tb1.field1 = tb2.field1 
       AND tb1.field1 = tb3.field1 
       AND tb1.field1 = "value"

I tested the code on MSAccess and SQL SERVER 2008

Upvotes: 6

Abhik Dey
Abhik Dey

Reputation: 403

Your problem is solved,just follow this what I have done-

create table tb1(user_level int); create table tb2(user_level int,username varchar(20));

insert into tb1 values(2); insert into tb2 values(2,'Mr.X');

I have this two tables like this where user_level is common,now according to you I tried to update the user_level column in both table using one query on a common criteria for both table i.e. username.

I tried to update the value of user_level column from 2 to 3 in both tables where the username is 'Mr.X' using a single query,so I tried the following query and it perfectly worked..

          update tb1 inner join tb2

          on tb1.user_level=tb2.user_level 

              set tb1.user_level=3,

                  tb2.user_level=3

             where tb2.username='Mr.X' ;

Upvotes: 5

Jorge Campos
Jorge Campos

Reputation: 23361

Try this: 

update db1 inner join db2 on db1.username = db2.username 
   set db1.user_level = 1,
       db2.user_level = 1
  where db1.username = 'a';

See it here on fiddle: http://sqlfiddle.com/#!2/ba34ac/1

Upvotes: 3

Related Questions