patternsqlModerate

If exists then update else insert

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

insertupdateelsethenexists

Problem

I am trying to create a STORED PROCEDURE that will be used to UPDATE a table called machine. This table has three columns (machine_id, machine_name and reg_id).

In aforementioned table,reg_id (INT) is a column whose values can be changed for a machine_id.

I would like to define a QUERY/PROCEDURE to check if a reg_id already exists in that table. If it does, then UPDATE that row, otherwise INSERT a new row.

Can someone please help me to write that QUERY/PROCEDURE?

Solution

Only problem is, you can't use it like a normal query. Control structures like IF or WHILE are only allowed in stored procedures or functions.

Just create a procedure like this:

delimiter $
create procedure select_or_insert()
begin
  IF EXISTS (select * from users where username = 'something') THEN
    update users set id= 'some' where username = 'something';
  ELSE 
    insert into users (username) values ('something');
  END IF;
end $
delimiter ;

and call it like this:

call select_or_insert();

and Done

Code Snippets

delimiter $$
create procedure select_or_insert()
begin
  IF EXISTS (select * from users where username = 'something') THEN
    update users set id= 'some' where username = 'something';
  ELSE 
    insert into users (username) values ('something');
  END IF;
end $$
delimiter ;

call select_or_insert();

Context

StackExchange Database Administrators Q#99120, answer score: 17

Revisions (0)

No revisions yet.