MySQL: insert procedure, with variable from another table-CodePudding

I have two tables:

CREATE TABLE userTypes (
    id INTEGER NOT NULL PRIMARY KEY,
    type VARCHAR(50) NOT NULL
);

CREATE TABLE users (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(50) NOT NULL,
    userTypeId INTEGER NOT NULL,
    FOREIGN KEY (userTypeId) REFERENCES userTypes(id)
);
  
INSERT INTO userTypes (id, type) VALUES (0, 'free');
INSERT INTO userTypes (id, type) VALUES (1, 'paid');

I want to create a procedure where that it inserts a user in the users table, with :

id is auto incremented.
email is equal to the email parameter.
userTypeId is the id of the userTypes row whose type attribute is equal to the type parameter

The INSERT function doesn't work with WHERE, so I tried to add a UPDATE but it's not working. Here's what I have for the moment:

DELIMITER //
CREATE PROCEDURE insertUser(
    IN type VARCHAR(50),
    IN email VARCHAR(50)
)
BEGIN
    INSERT INTO users(id, email, userTypeID) VALUES (LAST_INSERT_ID(), email, userTypeID);
    UPDATE users SET users.userTypeID = usertypes.id
WHERE usertypes.type = type;
END//
DELIMITER ;

The expected result should be something like this:

CALL insertUser('free', '[email protected]');
CALL insertUser('paid', '[email protected]');

SELECT * FROM users;


id    email                   userTypeId
------------------------------------------
1     name_1@mail.com         0
2     name_2@mail.com         1

CodePudding user response：

Leave out the auto_increment-column. As the name suggests, the db will fill this automatically.

Then, use different names for the parameters than the column names. You can use a prefix with the parameters.

Additionally, you could consider using the userTypeId integer value as parameter instead of the textual value. Otherwise you need to handle the situation where the passed type is not among the types in the userTypes (create a new one/reject insert).

DELIMITER //
CREATE PROCEDURE insertUser(
in_type VARCHAR(50),
in_email VARCHAR(50)
)
BEGIN

INSERT INTO users(email, userTypeID) 
SELECT in_email, id
FROM userTypes
WHERE type=in_type;

IF (ROW_COUNT()=0) THEN
  SELECT 'Error';
ELSE
  SELECT 'OK';
END IF;
  
END//
DELIMITER ;

CodePudding user response：

Something like this might work.

INSERT INTO users(email, userTypeID) 
VALUES(@email, (SELECT userTypeId from UserTypes WHERE usertypes.type = type));