Create a stored procedure in mysql which deletes the row if exists, if not insert an value

I have written the below procedure but when it executes the table always keep only one data in it and rest is deleted whenever a call made to the Stored Procedure.

So finally only the latest value is stored and the rest all gets deleted, so i end up in getting only one result data from my table .

Is there any thing wrong i am doing in the below procedure?

CREATE PROCEDURE `PersonVisitInfo`(personid varchar(254),visits int )
BEGIN
declare _personid varchar(254);
declare _visit_count int;
declare oldest_item varchar(254);

set _personid   = personid;
set _visit_count = visits;

delete from personData where personid = _personid ;

if (select count(*) from personData where personid = _personid) >= _visit_count then
    SELECT id into oldest_item FROM personData  WHERE personid = _personid ORDER BY lastvisitdate LIMIT 1;
    DELETE开发者_运维百科 FROM personData WHERE id = oldest_item; 
end if;

insert into personData(id, personid, lastvisitdate) values(UUID(), _personid, now());

END$$

---

You need to remove this line:

delete from personData where personid = _personid ;

This removes all the person's records from the table, meaning that the following if statement will always be false, and the last line will insert the new record - which will be the only record for that person.

---

delete from personData where personid = _personid ;

if (select count(*) from personData where personid = _personid) >= _visit_count

But you've just deleted all the rows - so the 'if' statement will never be true.

Leaving that aside, why not just have a unique key on personid and use 'REPLACE' or 'INSERT....ON DUPLICATE KEY UPDATE' instead: