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?



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

| improve this answer | |
  • They may need reg_id = VALUES(reg_id) as well. – ypercubeᵀᴹ Apr 29 '15 at 12:38
  • what if we have two conditions on query, I mean not only key is unique? – Sam Feb 15 '18 at 19:54

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()
  IF EXISTS (select * from users where username = 'something') THEN
    update users set id= 'some' where username = 'something';
    insert into users (username) values ('something');
end $$
delimiter ;

and call it like this:

call select_or_insert();

and Done

| improve this answer | |
  • 1
    Hello @Taz, can you check off the answer you went with? – Craig Efrein May 12 '15 at 11:41
  • @Craig Efrein hi. i went with the first one. Answer of yours. Thanks again. – Taz May 12 '15 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. – Bill N. Varelli May 12 '15 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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