Mysql – Multiple select for single row insert

mysql-5.5mysql-5.6postgresql

I need to insert a row into a table by selecting value from multiple tables. I want a row to be inserted from multiple tables.

insert into tab(id,name,date) values
((select max(id)+1 from tab),(select name from tab1),now());

Can you tell me how to do achieve this?

Whether this can be achieved by simple query itself or stored procedure is the only option?

Best Answer

This is typically achieved by writing a SELECT statement that outputs the fields in the same order as the INSERT statement and using that instead of a VALUES clause. For example, one might:

INSERT INTO my_table (field1, field2)
SELECT t1.field1, max(t2.field2)
FROM t1 INNER JOIN t2 ON (...)
GROUP BY t1.field1
WHERE something_else = 'whatever';

The point is that you can just do an

INSERT INTO ... SELECT ...

to insert rows. You do not have to create a VALUES clause and mess with subqueries.

Related Question