CODEWITHSUNDEEP
mysqlsqlmariadb
random dreamer
random dreamer

Reputation: 237

insert when one value is selected from other table

I would like to insert new values into a table where one of the values is being selected from another table with a condition, and the other value is a constant (hardcoded)

this command gives a syntax error

INSERT INTO table1 (itemId, reservedId) VALUES (SELECT id FROM table2 WHERE condition, 213) ;

error message : syntaxe error at line 2

Upvotes: 3

Views: 115

Answers (3)

Lajos Arpad
Lajos Arpad

Reputation: 76593

You can either use

insert into yourtable(...) values(...)[, (...)...]

or

insert into yourtable(...) select ...

Your mistake was that you mixed up the two syntaxes. You will not need values(...) here wrapped around your select:

INSERT INTO table1 (itemId, reservedId) 
SELECT id, 213 
FROM table2 
WHERE condition;

Notice that 213 is a value for reservedId and has nothing to do in the where clause.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311438

You can't mix values and select statements like this, as you've seen. You could, however, select the literal values you want to insert from the same table (note that you should not have a values clause - it's replaced by the select statement):

INSERT INTO table1 (itemId, reservedId)
SELECT id, 213 FROM table2 WHERE condition;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269933

The syntax is INSERT . . . SELECT:

INSERT INTO table1 (itemId, reservedId) 
     SELECT id, 123
     FROM table2
     WHERE condition ;

No VALUES is needed.

Upvotes: 4

Related Questions