SQL INSERT INTO select

Question

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.

Answer 1

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,