In many cases, there are more than one way to join two tables; See the other answers for lots of examples. Of course, one could say that it would be an error to use the 'automatic join' in those cases. Then only a handfull of simple cases where it can be used would be left.
However, there is a severe drawback! Queries that are correct today, might become an error tomorrow just by adding a second FK to the same table!
Let me say that again: by adding columns, queries that do not use those columns could turn from 'correct' into 'error'!
That is such a maintenance nightmare, that any sane style guide would prohibit to use this feature. Most already prohibit select * for the same reason!
All this would be acceptable, if performance would be enhanced. However, that's not the case.
Summarizing, this feature could be used in only a limited set of simple cases, does not increase performance, and most style guides would prohibit its usage anyway.
Therefor it is not supprising that most database vendors choose to spend their time on more important things.
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 ;
Best Answer
If you are editing data through the editor(mysqlworkbench) it needs a table on where it should execute the update command and the where column. That's why you can edit the record only when you have executed
SELECT * FROM tablebecause result of this query has primary key and table So Updating the data through workbench will be allowedIf you are using join to get the record then the editor displays the record of both the tables. And workbench(editor) won't get the table and primary key to update.