UPDATE SELECT from two tables

This query is known to copy the $id row in the table into the table itself.
If, say, val1is in another table, what would the query look like?

INSERT INTO `table` t
VAlUES (val1, val2,...valN) 
SELECT 
(SELECT val1 FROM `table1` t1 WHERE t1.id=t.nid)
, val2,...,valN 
FROM `table` t
WHERE id=$id

So will it go wrong? The thing is that val1is located in another table, and its id is written to the main table in the nidfield.

And what about UPDATE if a copy of the row already exists, but needs to be updated.


Answer 1, authority 100%

And what about UPDATE if a copy of the row already exists, but needs to be updated.

in theory, you need to make a unique index for fields that do not need to be repeated, and then

INSERT INTO `table` (a,b,c,d) VALUES (q,w,e,r) ON DUPLICATE KEY UPDATE `a` = `a`+1

and about the first part of the question – it seems to work, the main thing is that the number of columns in the nested query matches the number of columns inserted. Syntax:

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID 
    FROM tblTemp1
    WHERE tblTemp1.fldOrder_ID > 100;

Answer 2, authority 50%

1 – Get the syntax Insert On Duplicate Key Update

2 – Try to never use subqueries in mysql. If it is possible of course. Mysql is dumb and doesn’t know how to optimize them, do it via join.


Answer 3

INSERT INTO T(...)
SELECT * FROM A 
INNER JOIN B ON A.AA = B.AA
INNER JOIN C ON C.DD = B.DD
LEFT OUTER JOIN ....

In general, a SELECT can be as complex as you like, as long as the number of columns matches the number listed in the INSER header and their types are castable to the types of the target columns. By the way, just in case, it is advisable to specify the DBMS.