JLome JLome - 6 months ago 12
SQL Question

Using the result of a SELECT query as a value in an INSERT query

Is it possible to use the result of query as the value of a field in an INSERT query? This is what I'd like to achieve but am I going about it the right way?

INSERT INTO tblCounts ( CategoryID, GroupID, CountNo )
VALUES (DMAX("CategoryID","tblCategories"), (SELECT GroupID from tblGroups), 0);

Answer

You should use this:

INSERT INTO tblCounts ( CategoryID, GroupID, CountNo )
select DMAX("CategoryID","tblCategories"), GroupID, 0
from tblGroups;

But it seem that DMAX is Access function, not MySQL?

For condition with a CountNo of 3 and CategoryID of 2 you should add where clause to the select query like this:

INSERT INTO tblCounts ( CategoryID, GroupID, CountNo )
select DMAX("CategoryID","tblCategories"), GroupID, 0  -- select 2, GroupID, 3 from tblgroups where CategoryID = 2 and CountNo = 3--?
from tblGroups
where CategoryID = 2 and CountNo = 3;