After your query runs, you'll have a set of copied rows (with configid=41
) and an identical set of pasted rows (except for the configid=76
and the auto-created id
).
Since, these ids are not known in advance, you'll need another way to identify rows of the config table, e.g. a unique key (besides the auto-incrementing one), so you can match (join) the newly created rows with the old ones.
If, for example, the (configid, optionname)
is unique, then the following would work:
INSERT INTO pricing
( relid, price, ... ) --- relid and all the other columns,
--- except any autoincrement you may have
SELECT pasted.id, p.price, .... --- and the same columns here
FROM
pricing AS p
JOIN
tblproductconfigoptionssub AS copied
ON copied.id = p.relid
AND copied.configid = 41
JOIN
tblproductconfigoptionssub AS pasted
ON pasted.optioname = copied.optioname
AND pasted.configid = 76 ;
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
First of all, the fact that two tables don't have
id
s in common does not mean you cannot join them. You canJOIN
two tables using any boolean expression. This can betable1.id = table2.id
, but in can also betable2.latitude = table1.lat AND table2.longitude = table1.latitude
.Let's assume these are your tables:
We could perform your original query with a subquery, and get:
(Nearly) the same can be done with a
JOIN
through the following query:(If you want to get the row with no match on table2, you can use
LEFT JOIN
instead ofJOIN
).If you want to
UPDATE
table1.id
to be the same astable2.id
, you can do so on MySQL by using:Note that this is not standard SQL, which does not allow to have a
JOIN
on this part of anUPDATE
. If more than one matching row exists ontable2
, the value used for theupdate
could be any of the matching ones.The standard SQL way to do the same thing is:
Or:
You can check everything at dbfiddle here