How to check if the record exists in the database and update if it exists

Question

How to check if the record exists in the database and update if it exists I tried this way

Select if((SELECT COUNT(*) FROM Pessoas WHERE id = 0) > 0, 
          ( UPDATE Pessoas SET Name ="João" where id = 0 ),
          ( INSERT INTO Pessoas VALUES (0,"João")  )
);

Answer 1

Usually for these cases I resort to stored Procedure to perform these control operations, e.g.:

CREATE DEFINER=`root`@`localhost` PROCEDURE 'gravar_pessoas'(zid int, znome varchar(100))
begin
   if not exists(select * from pessoas where id = zid) then
     insert into pessoas() values(zid, znome);
   end if;

   if exists(select *  from pessoas where id = zid) then
      update pessoas set nome = znome where id = zid;
    end if;
end

Answer 2

It sounds strange to me before the appointment you already have the id and still check if it exists in the database. But let’s go to the answer!

---

Whereas you already have the id of the record to be searched and that id almost always is a primary key or unique, you can run the following query:

INSERT INTO `pessoas` (`id`, `Name`) VALUES (0, 'João') ON DUPLICATE KEY UPDATE `Name` = 'João';

That’s exactly what you’re doing at query of your question:

If there is some record with the id = 0, update your Name = 'João'.

If not, insert the id = 0 with Name = 'João'.

Answer 3

select if((select count(*) from Pessoas where id = 2) > 0, 
(UPDATE Pessoas SET Name ="João" where id = 0), 
INSERT INTO Pessoas VALUES (0,"João")) as value from Pessoas