CODEWITHSUNDEEP
mysqlperformancejoin
CKY
CKY

Reputation: 45

How to Update Two tables with single JOIN statement in MYSQL server?

I have read that A JOIN statement can connect two tables for update and I'm having a hard time warping my brain around it..

I have 7 tables and growing all different languages all different charsets. I need to update and insert 3 columns..

sudo naming

The tables are 100% the same structure..

To do an update now I use this query

UPDATE `Table1EN` SET `Details`= 'String Value', `Name` = 'String Name',
 `Info`= 1 WHERE `ID` = 1;

and then repeat 5000 times and change table1EN to table1** and re-run that

Is there a way to simplify this to? 

UPDATE `Table1EN`,`Table1ZH`,`Table1DE`,`Table1FR`,`Table1RU`,`Table1ES`,`Table1PT` 
SET `Details`= 'String Value', `Name` = 'String Name', `Info`= 1 WHERE `ID` = 1;

Running this query returns ambiguous name details, name and info. using a join statement seems to lock up the server..

My questions are how can I run a multi-table update query where all values are the same nothing changes? Not lock up the server? Not have an ambiguous name error? Finally not have to run the query in chucks of 5000 after renaming the table?

Update1:

As Arth has pointed below, I did not include the actual JOIN Query here.

    UPDATE table1EN 
    INNER JOIN table1ZH USING (ID) 
    INNER JOIN table1DE USING (ID) 
    INNER JOIN table1FR USING (ID) 
    INNER JOIN table1PT USING (ID) 
    INNER JOIN table1ES USING (ID) 
    INNER JOIN table1RU USING (ID) 
    SET table1EN.Info = 1, table1EN.Details ='String Value',
     table1ZH.Info = 1, table1ZH.Details ='String Value',
     table1DE.Info = 1, table1DE.Details ='String Value',
     table1FR.Info = 1, table1FR.Details ='String Value',
     table1ES.Info = 1, table1ES.Details ='String Value',
     table1RU.Info = 1, table1RU.Details ='String Value',
     table1PT.Info = 1, table1PT.Details ='String Value'
     WHERE table1EN.ID = 1;

I'm posting it now in hopes to simplify it to stop the server from crashing anytime I try to run it with 5000 different query's at one time.

I have tried reducing this based on something I read

to 

    set table1EN.Info = 1, table1EN.Details ='String Value', 
    table1ZH.Info=table1EN.Info,
    table1DE.Info=table1EN.Info,
    table1FR.Info=table1EN.Info 
    etc ........

However this seems to cause even more server lag and crashes witch I would expect..

Upvotes: 1

Views: 368

Answers (2)

xQbert
xQbert

Reputation: 35343

I wonder if....

Update (
Select ID, details, name, info, 'table1EN' src FROM table1EN UNION ALL
Select ID, details, name, info, 'table1ZH'     FROM table1ZH UNION ALL
Select ID, details, name, info, 'table1DE'     FROM table1DE UNION ALL
Select ID, details, name, info, 'table1RU'     FROM table1RU UNION ALL
Select ID, details, name, info, 'table1FR'     FROM table1FR UNION ALL
Select ID, details, name, info, 'table1ES'     FROM table1ES UNION ALL
Select ID, details, name, info, 'table1PT'     FROM table1PT) b 
set `details` = 'String value', 
    `Name` = 'String Name', 
    `Info` = 1 
where ID=1

would work... based on https://dev.mysql.com/worklog/task/?id=3701

ID and src would be a composite key.

Upvotes: 1

Arth
Arth

Reputation: 13110

First off when you do the UPDATE JOIN, you haven't included any JOIN conditions.. so that will try and do a cartesian product of each table:

#rows =  #rows_table1EN * #rows_table1ZH * ... * #rows_table1PT

You should JOIN on table1EN.id = table1xx.id for each table.

The next problem is that you will have to reference each column you want to change. This will result in:

SET table1EN.detail = 'String Value',
    table1ZH.detail = 'String Value'
    ...
    table1PT.detail = 'String Value'

This could be done with dynamically building the statement, but that is fairly hideous.

This all leads me to question your database structure. Did you consider using one table with an extra language column; either the two letter identifier (OKish) or a foreign key to a languages table (better)?

Upvotes: 1

Related Questions