MySQL：将多行数据插入表中，一些数据来自另一个表（关系型）

Question

Additional question to MySQL: Insert data into table, some data comes from another table (relational)

My question above basically asked "how can I insert a row into a table, using information found in another row". The answer was to use a query such as:

INSERT INTO user_config(cid, sid, value)
SELECT $cid, id, $value
FROM user_settings
WHERE mid='$mid' AND name='$name'
ON DUPLICATE KEY UPDATE value = $value;

My problem now is, I need to insert multiple rows. For each row I need to insert, there is a different value for $cid, $value, $mid and $name. For example I could run the following (pseudo):

function x($cid, $mid, $name, $value){
    // run query:
    INSERT INTO user_config(cid, sid, value)
    SELECT $cid, id, $value
    FROM user_settings
    WHERE mid='$mid' AND name='$name'
    ON DUPLICATE KEY UPDATE value = $value;
}

x(1,'a','b');
x(2,'c','d');
x(3,'f','e');

Problem is, that would run 3 queries, and I want to be able to do this in one. Is it possible? I could provide an array for $cid, $mid and $name if that helps the problem?

Answer 1

Presumably the information in the x calls is expressible as a select on various tables in your database. Your list of x calls looks le a table whose columns are $cid, $mid, $name, $value, right? (Your x calls should have 4 arguments not 3.)

Give the definitions of the tables and a query that produces the x arguments as a table with columns cid, mid, name, value.

Then modify the select in your question so that that x select is in the FROM. Of course, you might not have to express the update using a subselect; I am just describing the change in terms of adding syntax to the sytax you gave.

UPDATE

You can make a (possibly temporary) table as the x select. Put that table in the FROM. If the table is around while you collecting multiple x argument lists then you can just insert each (cid,mid,name,value) as you get it.

INSERT INTO user_config (cid, sid, value)
SELECT cid, id, value
FROM user_settings s JOIN x
ON s.mid=x.mid AND s.name=x.name
ON DUPLICATE KEY UPDATE value = x.value;