MySQL: insert procedure, with variable from another table

Question

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     [email protected]         0
2     [email protected]         1

slaakso · Accepted Answer · 2021-11-21 09:26:20Z

2

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 ;

answered Nov 21, 2021 at 9:26

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Svein Terje Gaup Over a year ago

You might also handle the situation where there are duplicates in the userTypes type column, since there seems to be no unique constraint on that column. If there were, your insert would do multiple inserts.

slaakso Over a year ago

As said, it would be better to use the userTypeId as a parameter to the routine than the actual name for it.

Collectives™ on Stack Overflow

MySQL: insert procedure, with variable from another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related