CODEWITHSUNDEEP
mysqlsqlselectinsert
elzaer
elzaer

Reputation: 729

SQL INSERT INTO select

Currently using a MySQL database

Wanting to insert a new row into a table (t_2) for every matching entry in my where condition of another table (t_1).

I also want to include a count value from a seperate table for each entry (count from table counter) and a string value 'decrease' for each entry. No idea how to put this through, this is what I have so far:

INSERT INTO t_2(count,id,val='decrease')
SELECT MAX(count) as count FROM counter
SELECT id FROM t_1 WHERE val < 0

the error I am getting is:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '='decrease') SELECT count FROM counter SELECT id FROM t_1 WHERE val < 0' at line 1

I know I probably shouldn't have the val='decrease' but just wanted to illustrate I want that to be a string value inserted for each new row.

Upvotes: 3

Views: 1247

Answers (2)

Marco
Marco

Reputation: 57603

Try this:

INSERT INTO t_2 (count,id,val) 
SELECT 
    (SELECT MAX(count) FROM counter),
    t_1.id, 
    'decrease'
FROM t_1 
WHERE val < 0,

Upvotes: 3

detale
detale

Reputation: 12930

Is this what you're looking for?

INSERT INTO t_2(count,id,val)
    SELECT (SELECT MAX(count) as count FROM counter) as count, id, 'decrease' as val
    FROM t_1
    WHERE val < 0

Upvotes: 3

Related Questions