RUN IF ELSE STATEMENT in MYSQL to run different querys

Question

I need to run a query with an if else statement(if possible in MYSQL)

I want to: SELECT id FROM user_name_table WHERE user_name = @name;

If the result of the query returns null i want to insert @name to user_name_table and get the autoincrement generated id i need to store this result in a variable to use it later in another insert query.

This is as far as i got, in this case if "Sam" exists will return the ID twice, if not it will return ID and '0'

SET @name = "Sam";

SELECT id, IF(id!=0, id, 0)
FROM user_name WHERE user_name = @name;

Do you run the query through an app? I mean like, are you building an app with this query part of the function? — FanoFN
– FanoFN, Commented Jan 6, 2021 at 6:21
Yes, i'm planning to run this on a stored procedure by just passing the user_name — Christopher Martinez
– Christopher Martinez, Commented Jan 6, 2021 at 14:59

Gordon Linoff · Accepted Answer · 2021-01-06 13:22:19Z

3

If you don't want duplicates for name, then add a unique constraint or index:

alter table user_name add constraint unq_user_name_name unique (name);

Then, if you want to insert a new name, you can simply use:

insert into user_name (name)
    values (@name);

This will return an error if there is a duplicate. If you don't want an error, I would suggest:

insert into user_name (name)
    values (@name)
    on duplicate key update name = values(name);

answered Jan 6, 2021 at 13:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

wally Over a year ago

Or INSERT IGNORE - mysqltutorial.org/mysql-insert-ignore

Gordon Linoff Over a year ago

@wally . . . on duplicate key update only ignores errors that are due to violations of unique constraints. insert ignore ignores other errors, which I don't think is a good idea.

Collectives™ on Stack Overflow

RUN IF ELSE STATEMENT in MYSQL to run different querys

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