insert when one value is selected from other table

user_9315977 0
Viewed 51

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

mariadb mysql sql
3 Answers

The syntax is INSERT . . . SELECT:

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

No VALUES is needed.

user_11440350

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;
user_24227760

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.

user_4365600
Related