12

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?

12

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

  • 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
10

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

  • 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.