17

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?

0

2 Answers 2

17

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

4
  • 1
    Hello @Taz, can you check off the answer you went with? May 12, 2015 at 11:41
  • @Craig Efrein hi. i went with the first one. Answer of yours. Thanks again.
    – Taz
    May 12, 2015 at 12:25
  • @Taz I am glad that this information is helpful, please if this answer could remove your doubts proceed to close the question. May 12, 2015 at 13:25
  • MYSQL doesn't have an command of IF EXISTS that can be used. There is an EXISTS that you can put into a WHERE clause but I can't see an EXISTS that would work like this in the mysql documentation
    – Luke
    Nov 24, 2022 at 20:25
13

Hope This helps, DUPLICATE KEY UPDATE

create table machine(
  machine_id int not null primary key,
  machine_name varchar(50),
  reg_id int
);

insert into machine (machine_id, machine_name, reg_id)
values(1, 'my_machine', 1);

INSERT INTO machine (reg_id, machine_id, machine_name) VALUES (1, 1, 'test_machine')
  ON DUPLICATE KEY UPDATE machine_name=VALUES(machine_name);

Work on SQL Fiddle

2
  • They may need reg_id = VALUES(reg_id) as well. Apr 29, 2015 at 12:38
  • what if we have two conditions on query, I mean not only key is unique?
    – Samir
    Feb 15, 2018 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.