Mysql – If exists then update else insert

MySQL

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?

Best Answer

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

Related Solutions

MySQL – Get User’s Rank from Equation and Store in Table

OK - this should be easy for the first part, if you're willing to be a bit adventurous :-).

MariaDB has had computed columns (otherwise known as virtual or generated columns) for quite a while now. Otherwise, the latest milestone release of MySQL (5.7.7) also has them.

If you want to do this with a current GA version, you'll have to use TRIGGERs.

However, if you want to update the RANK (see a lovely post here) on each update of games_won/games_lost, then you'll have to call a PROCEDURE which will update RANK. MySQL's Stored Procedure programming language isn't sophisticated enough for this on its own, but see here for some ideas. It says that there can be rollback issues, but since you're updating only one table, it should be easier than most - good luck :-). BTW, I'd use float/double for games_won/_lost - otherwise, you might have problems with integer division.

Unable to Update Table After Creating an AFTER UPDATE Trigger in MySQL

Thats correct you can not update the same table where the trigger is getting executed, however if you use before update you can set the st to 0 by checking the conditions something as

DELIMITER $$

USE `smsdev`$$

DROP TRIGGER /*!50032 IF EXISTS */ `pageprivilege_update`$$

CREATE
    /*!50017 DEFINER = 'smsdev'@'%' */
    TRIGGER `pageprivilege_update` BEFORE UPDATE ON `pageprivilege` 
    FOR EACH ROW 
    BEGIN
     if (new.ad = 0 AND new.ed =0 AND new.dl =0 AND new.rd = 0) then
       set new.st=0 ;
     end if;
END;
$$

DELIMITER ;

Best Answer

Related Solutions

MySQL – Get User’s Rank from Equation and Store in Table

Unable to Update Table After Creating an AFTER UPDATE Trigger in MySQL

Related Question