CODEWITHSUNDEEP
mysqlsqldatabase
user4181107
user4181107

Reputation: 401

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

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

Upvotes: 0

Views: 80

Answers (2)

shawnt00
shawnt00

Reputation: 17915

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.

Upvotes: 0

ASh
ASh

Reputation: 35680

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;

Upvotes: 3

Related Questions