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               jack@example.org
    2          1                 thing              john@example.org
    3          3                 goes               annete@example.net
    4          2                 here               bilbo@example.com

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'), 
    'kurt@example.net');

and that will add kurt@example.net 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'), 
'kurt@example.net' );

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'), 
     'kurt@example.net'
 );

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