How to get the RowID to put into an INSERT INTO statement

Question

I have two tables
virtual_domains
virtual_users.

virtual_domains has a list of valid domains for my emails server, example data:

    ID         Name
    1          example.org
    2          example.com
    3          example.net

virtual_users keeps the valid email addresses for each domain, and links them to the domain which they belong, example data:

    id         domain_id          password           email address
    1          1                 some               [email protected]
    2          1                 thing              [email protected]
    3          3                 goes               [email protected]
    4          2                 here               [email protected]

So to insert a new user, I use this syntax (auto_increment is in use for the id column):

INSERT INTO databasename.virtual_users (
    id, 
    domain_id, 
    password, 
    email ) 
    VALUES (
    DEFAULT, 
    '3', 
    MD5('somepassword'), 
    '[email protected]');

and that will add [email protected] to the 5th row as an email user.

What I want to be able to do, is instead of putting in the '3' for the domain id, add some other syntax there to return the id from the virtual_domains table of the relevent domain, ie:

INSERT INTO `databasename`.`virtual_users` ( 
`id`, 
`domain_id`, 
`password`, 
`email` ) 
VALUES (
DEFAULT, 
*ADD_QUERY_HERE_TO_GET_DOMIAN_ID_VALUE_FROM_virtual_domains_TABLE*, 
MD5('somepassword'), 
'[email protected]' );

so that it can all be done in one command, rather than having to look up the domain_id in a seperate query.

Are you adding to virtual_domains at the same time, or are those old values which don't change? — Dave, Jun 01 '14 at 09:53

score 1 · Accepted Answer · answered Jun 01 '14 at 10:29

You can use a subquery in an INSERT statement.

 INSERT INTO databasename.virtual_users (
     id, 
     domain_id, 
     password, 
     email
 ) 
     VALUES (
     DEFAULT, 
     ( SELECT ID FROM databasename.virtual_domains WHERE Name = 'example.net' ), 
     MD5('somepassword'), 
     '[email protected]'
 );

I have only tested this in SQL Server, but I think it should work in most databases.

How to get the RowID to put into an INSERT INTO statement

1 Answers1