As others have pointed out, this is a Really Bad Idea. Still, if you insist, the SQL is not hugely complicated:
CREATE TABLE RawData
(
PartNumber VARCHAR(30) NOT NULL PRIMARY KEY,
Manufacturer VARCHAR(30) NOT NULL,
Data1 VARCHAR(30),
Data2 VARCHAR(30),
Data3 VARCHAR(30)
)
CREATE TABLE Translations
(
FieldName VARCHAR(30) NOT NULL,
Value VARCHAR(30) NOT NULL,
PRIMARY KEY (FieldName, Value),
ID INT NOT NULL
UNIQUE (FieldName, ID)
)
CREATE TABLE CleanData
(
PartNumber VARCHAR(30) NOT NULL PRIMARY KEY,
Manufacturer VARCHAR(30) NOT NULL,
Data1 VARCHAR(30),
Data2 VARCHAR(30),
Data3 VARCHAR(30)
)
INSERT INTO CleanData (PartNumber, Manufacturer, Data1, Data2, Data3)
SELECT
RD.PartNumber,
TMfr.ID AS Manufacturer,
TDt1.ID AS Data1,
TDt2.ID AS Data2,
TDt3.ID AS Data3
FROM
RawData AS RD
LEFT JOIN Translations AS TMfr ON RD.Manufacturer = TMfr.Value AND TMfr.FieldName = 'Manufacturer'
LEFT JOIN Translations AS TDt1 ON RD.Data1 = TDt1.Value AND TDt1.FieldName = 'Data1'
LEFT JOIN Translations AS TDt2 ON RD.Data2 = TDt2.Value AND TDt2.FieldName = 'Data2'
LEFT JOIN Translations AS TDt3 ON RD.Data3 = TDt3.Value AND TDt3.FieldName = 'Data3'
Extend to the complete set of fields. May Codd have mercy on your soul.
The question is a unclear on how the tables are to actually related to each other.
Since the query you've posted is not working, it's difficult to tell which element of the logic contains the error, and with a column called "oldID" it's hard to understand the necessary join logic.
I have attempted to interpret and rewrite your query based on trying to understand it from the innermost query to the outermost query, it seems like this might be what you are trying to accomplish.
My table "ta" is doing the same thing as your table "ta" seems to be trying to do, and my table "a" is doing the same thing your table "a" seems to be trying to do. They are reversed in their order of appearance in the query because, as I mentioned, if I'm understanding your query correctly, the original query seems to be written inside-out.
UPDATE Taxonomy t
JOIN TaxonomyMapping ta ON ta.oldID = t.oldID
JOIN CategoryMapping cm ON cm.language_ID = ta.oldID
JOIN TaxonomyMapping a ON a.oldID = cm.en_id
SET t.sourceId = a.id
WHERE t.id > -1;
However, if your "TaxonomyMapping" and "Taxonomy" tables are an "exact copy" (containing the same data) then table "ta" is not necessary, and all you actually need is this:
UPDATE Taxonomy t
JOIN CategoryMapping cm ON cm.language_ID = t.oldID
JOIN TaxonomyMapping a ON a.oldID = cm.en_id
SET t.sourceId = a.id
WHERE t.id > -1;
In fact, it seems like you could even do it without a permanent copy.
UPDATE Taxonomy t
JOIN CategoryMapping cm ON cm.language_ID = t.oldID
JOIN (SELECT * FROM Taxonomy) x ON x.oldID = cm.en_ID
SET t.sourceId = x.id
WHERE t.id > -1;
That last example works because the server materializes a temporary copy of "Taxonomy" called table "x" before executing the rest of the query. By doing that, it's able to update the table with data selected from elsewhere in the same table, which you normally can't do... but since a copy is technically a different table, it works. The temporary copy is usually created in memory and then moved to disk if it turns out to be large, but either way, it's automatically deleted as soon as the query is finished. The down-side is that the new table won't have any indexes so this approach won't infinitely scale... but should still be faster than your original correlated subquery.
Of course, none of these will work if I have failed to understand the necessary logic.
Best Answer
The left-join variant:
http://sqlfiddle.com/#!9/01d421/7 - returns only 3 rows because your sample data does not contain the EN translation for all news as your real data are supposed to.
For completeness, there is a version which picks first available translation (in order 'GER', 'EN') by a dependent subquery - http://sqlfiddle.com/#!9/01d421/9 (in this case it picks the German translation where there is no English one in the sample data, so results differ)