CODEWITHSUNDEEP
mysql
rahlzel
rahlzel

Reputation: 41

MySQL: Update multiple tables with different column names?

I'm trying to change two values in two tables using one query, but the column names for both tables is different. However, they are both primary keys.

So far I've tried a large number of variations of code, and here's a similar variation rewritten in simpler code so my purpose is more clear:

update table1, table2
set table1.entry = 5, table2.id = 5
where table1.entry and table2.id = 1;

The column names in both tables I want to change have different names (hence "entry" and "id"). I know how to do this using CONCATENATE in Excel, but I'd like to know if this be done with a query, perhaps with a JOIN?

Thanks in advance.

Upvotes: 2

Views: 1636

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Since both fields you want to update are primary keys, only one row will be updated in each table and there is no need for join:

update table1
set table1.entry = 5
where table1.entry = 1 ;

update table2
set table2.id = 5
where table2.id = 1 ;

Upvotes: 1

Karthik
Karthik

Reputation: 824

Thats really simple if you have any common criteria in both of the tables and can be accomplished by using a join. For Example assuming you have some common entity in both your tables and just use this query,

update table1 t1 join table2 t2 on t1.commonColumn=t2.commomColumn 
set t1.entry=5,t2.id=5 where t1.entry=1 and t2.id=2

Hope this helps!

Upvotes: 0

Delan Azabani
Delan Azabani

Reputation: 81442

I'm not sure I understand your question, but what about

update table1, table2
set table1.entry = 5, table2.id = 5
where table1.entry = 1 and table2.id = 1;

Upvotes: 2

Related Questions