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

Question

I want to run the following two queries in one:

SELECT id FROM user_settings WHERE ......
$id = id_from_query_above();
$value = 100; // this could be anything
INSERT INTO user_config (sid, value) VALUES($id, $value) ON DUPLICATE KEY UPDATE value=$value

(notice that I want to update if a row associating to the primary key has already been inserted).

Gordon Linoff · Accepted Answer · 2014-07-18 02:16:22Z

1

You want the insert . . . select syntax:

INSERT INTO user_config(sid, value)
    SELECT id, $value
    FROM user_settings
    WHERE ......
    ON DUPLICATE KEY UPDATE value = $value;

answered Jul 18, 2014 at 2:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Keir Simmons Over a year ago

That worked perfectly, thank you - I didn't know I could use the SELECT statement with non-table rows (i.e $value). Is it possible to do something similar with multiple insertions/ updates?

Gordon Linoff Over a year ago

This will do multiple insertions. I don't know what the question is about updates, but subqueries are allowed in update queries too.

Keir Simmons Over a year ago

I meant updates if the row has been inserted. Could you provide an example where this query can insert 2 rows?

Gordon Linoff Over a year ago

@KeirSimmons . . . That is an example. The select can return multiple rows.

Keir Simmons Over a year ago

So what about the value of $value (as this would be different for each insertion)?

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related