1

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

  • table1EN
  • table1DE
  • table1ZH
  • table1PT
  • table1FR
  • table1ES
  • table1RU

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

CKY
  • 45
  • 8
  • http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 or http://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query for mysql or... http://stackoverflow.com/questions/8851974/updating-multiple-tables-in-a-single-sql-statement using inner join syntax. or update though a union all view... https://dev.mysql.com/worklog/task/?id=3701 – xQbert Oct 08 '15 at 16:10
  • the fist link stats SQL can not do this, the second is for MySQL and is closer but dose Not explain how to use a single name in there example I would have to repeat the value for details name and info for every table in the query.. – CKY Oct 08 '15 at 16:13
  • The last one says to create view with a union ALL statement and update though the view. – xQbert Oct 08 '15 at 16:14
  • after reading about "After "CREATE VIEW v AS SELECT ... UNION ALL SELECT ...", v may be an updatable view." This cannot work because the unique key is the same on all tables and creating a new table from all tables is what this will do causing an error that the keys already present another suggestions? – CKY Oct 08 '15 at 16:25

2 Answers2

1

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)?

Arth
  • 12,789
  • 5
  • 37
  • 69
  • I did consider this but ran into BIG5 collisions where all other languages no matter the charset on the col would pick up the big5 char encoding and would return German letters with Chinese encoding even when using UTF-8 – CKY Oct 08 '15 at 16:33
  • There is no way to shorten the set to a single statement? – CKY Oct 08 '15 at 16:34
  • But why are you updating 7 tables all to the same value? Can you not just put that in 1 table even if it's just the `info`, `id` and `detail` and hang the other tables off that? I'd probably try and solve the language and collisions thing first, but would consider a table per charset over a table per language. – Arth Oct 09 '15 at 09:29
  • I'm inputting the information so that someone else can translate it to their native language.. so the information will not be the same after it has been translated... Each table has a default charset each column has a default charset.. When using the BIg5 charset you must set the table and column to BIG5 not UTF-8. when you set the table to utf8 in MyISAM you end up getting invalid characters that are unreadable by anyone.. A simple workaround was to just create a table for each language because in the calling statement i still have to tell the table to be read by BIG5 like so – CKY Oct 09 '15 at 14:39
  • query('SET NAMES "Big5" COLLATE "Big5_chinese_ci"'); – CKY Oct 09 '15 at 14:39
  • If the information is all different, why are you trying to `UPDATE` it all to the same value? At the very least you could have the base word in a separate table, and then add the translations separately as they come in. – Arth Oct 09 '15 at 14:57
  • here is a German sample of what you are talking about "St踀e" then here is a Chinese sample of what you are talking about "¿Ç¤l" this is done by simply moving "褲子" to a big 5 column in a utf-8 table. the table needs to maintain big5 and each column needs to be big5_chinese_ci. – CKY Oct 09 '15 at 14:59
  • Personally I haven't used non-utf-8 charsets and all my tables are innodb, but how many charsets do you need? Is it more than languages? – Arth Oct 09 '15 at 14:59
  • I have searched the inter-webs on a way to fix this the cleanest way was to create a new table for each charset and every language has there own charset so each language gets a different table... if you have a suggestion that i can have table 1 with the text columns being a different language then I'm all eyes.. I"m here asking for help because I have been unable to fix one issue and caused another..I will ask this in another question... – CKY Oct 09 '15 at 14:59
  • Ah ok, I am prepared to stand corrected on the language/charset front. I still don't know why you need to run this preliminary UPDATE though.. if people are translating on the fly, can't you just insert the data as it comes in? – Arth Oct 09 '15 at 15:01
  • sure I can but its easier for them to translate and overwrite the data in Heidi with a limited log in that only give them their language.. – CKY Oct 09 '15 at 15:04
  • You can give them the base word even if the translation doesn't exist! Otherwise you are storing a hell of a lot of redundant and incorrect translations. You can easily create a log of untranslated words using a left join. – Arth Oct 09 '15 at 15:09
  • After seeing that you my be correct in the way the table structure is or is not created correctly.I've posted this question to see if, maybe there is a way to fix this so that there is only one table however, there will still be redundint and incorectly translated data untill a translater comes in and fixes it ..http://stackoverflow.com/questions/33043577/mysql-charctor-encoding – CKY Oct 09 '15 at 17:13
1

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.

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • unless I misunderstood this is the error it returns.. #1288 - The target table b of the UPDATE is not updatable – CKY Oct 08 '15 at 18:05
  • Unfortunately I couldn't get it to work though a view either :( – xQbert Oct 12 '15 at 14:52
  • It was worth a shot. Like i said I'm open to trying anything to save a few hours of time.. – CKY Oct 12 '15 at 15:59