[RESOLVED] Insert or update...

mrbaseball34

I am passing values in a POST and have no idea if a record with some of the data exists already in the table.

here is the post:

$t =  $_REQUEST["teamid"];
$n =  $_REQUEST["name"];
$j =  $_REQUEST["jersey"];
$a =  $_REQUEST["age"];

Now, there will be 1 or more name, 1 or more jersey and one or more age values.
teamid will have one and only one value.

Now. when trying to insert/update the values, how would I do this?
In SQL Server, I can use an

if EXISTS(SELECT count FROM table WHERE name=:name) > 0
  BEGIN
    -- DO update here
  END
ELSE
  BEGIN
    -- DO insert here
  END

How to do this in mySQL?

REPLACE INTO and DUPLICATE KEY UPDATE don't look like they'll help here.
REPLACE INTO is slow and DUPLICATE KEY UPDATE won't work because we are
not sending the key value in the POST.

wilku

Is name unique or you can have more rows with the same name? For DUPLICATE KEY UPDATE it doesn't have to be the primary key. You can define unique key from a name as well. But only if the name is unique

mrbaseball34

I'm sure there can possibly be two Jimmy Smith's on a team

wilku

then this :

if EXISTS(SELECT count FROM table WHERE name=:name) > 0

doesn't make sense. How do you know who's posting - new Jimmy Smith and you have to insert new data or existing Jimmy Smith and you need to update. Something must be unique to decide whether you should insert a new one or update the existing.

mrbaseball34

Yes, I am now including a playerid as a hidden form field in the POST.

mrbaseball34

Got an answer from experts-exchange. Using a Stored Procedure to do everything.
example:

CREATE PROCEDURE update_team (jersey VARCHAR(50), name VARCHAR(50),age INT,teamid INT )
BEGIN
  DECLARE c  INT;
  SELECT count(*) INTO c
  FROM table1 WHERE `name` = name;
  IF (c  > 0) THEN
    UPDATE table1 SET `jersey` = jersey , `name` = name , `age` = age , `teamid`=teamid;
  ELSE
    INSERT INTO table1 SET `jersey` = jersey , `name` = name , `age` = age , `teamid`=teamid;
  END IF;
END;