What is the SQL to do a SELECT statement within an INSERT statement?

Question

I have the following tables in my MySQL database:

CREATE TABLE user_role (
    user_role_id INT UNSIGNED AUTO_INCREMENT,
    user_role VARCHAR(15) NOT NULL,
    CONSTRAINT pk_user_role PRIMARY KEY (user_role_id)
);

CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT,
    user_role_id INT UNSIGNED NOT NULL,
    email VARCHAR(254) NOT NULL,
    password VARCHAR(255) NOT NULL,
    CONSTRAINT pk_users PRIMARY KEY (user_id),
    CONSTRAINT fk1_users FOREIGN KEY (user_role_id) REFERENCES user_role (user_role_id),
    CONSTRAINT unq1_users UNIQUE (email)
);

Assuming all the user roles already exist, how can I insert a new user with the proper user role id (i.e. SELECT user_role_id WHERE user_role = 'role';) all in one statement?

I have tried the answer in the following question but that didn't work for me: INSERT INTO with SubQuery MySQL

You can't. Your `user_role` table doesn't have a column for the user. — Gordon Linoff, Feb 22 '15 at 15:49
Take a look at SQL `JOIN` statements. I am not sure if this satisfies what you need, but I think so — Cr41s3, Feb 22 '15 at 15:52

score 3 · Accepted Answer · answered Feb 22 '15 at 16:07

3

Assuming all the user roles already exist, you can insert a new user with the proper user role id (i.e. SELECT user_role_id WHERE user_role = 'role';) in one statement with this query:

insert into users (user_role_id, email, password)
select 
    user_role_id,
    'new_user',
    'password_hash'
from user_role r
where r.user_role='role'
limit 1;

answered Feb 22 '15 at 16:07

ASh

34,632
9
60
82

This works for me, I guess in the WHERE clause, I have to use table_name.column_name instead of just column_name. – user4181107 Feb 22 '15 at 16:19

score 0 · Answer 2 · answered Feb 22 '15 at 16:21

If subqueries work inside a values list you can try the first option below, but I suspect they don't. I'm also assuming colon is the parameter marker for your connection since.

insert into users (user_id, user_role_id, email, password)
values (
    :user_id,
    (select user_role_id from user_role where user_role = 'role'),
    :email,
    :password
)

Another option:

insert into users (user_id, user_role_id, email, password)
select
    :user_id,
    (select user_role_id from user_role where user_role = 'role'),
    :email,
    :password
--from dual or some other dummy table??

ASh's answer probably works great although I don't know why the limit 1 should be necessary. In that case you have two roles with the same name and you won't know which one comes back.

What is the SQL to do a SELECT statement within an INSERT statement?

2 Answers2