Real newb question.
Is there a command like UPDATE that will INSERT if the row does not exist or UPDATE if it does?
If not has someone got a favorite EXISTS bit of code.
Have finally realized after a good night’s sleep that I am trying to UPDATE non-existent rows 
TIA
Steve
Generally speaking (and it does depend upon which flavour of SQL you’re using), something like the following should work:
IF EXISTS(SELECT from table WHERE c=3)
BEGIN
UPDATE table SET a=1, b=2 WHERE c=3
END
ELSE
BEGIN
INSERT into table(a,b,c) VALUES(1,2,3)
END
@WolfShade ; Sorry have tried this 10 different ways in MySQL and cannot get it to work.
IF EXISTS(SELECT clientid FROM `client_phone` WHERE `clientid` = 22)
THEN
UPDATE client_phone SET `phone_number`=888, `country_code`=2 WHERE `clientid` = 22
Think that was the last way round I tried it (clientid DOES exist)
Sorry know I am being really dense but just cannot get the syntax right. Took the BEGIN and END out not sure if I should but made no difference.
EDIT here is the current code:
$sql =
'UPDATE client, client_email, client_phone
SET
first = :first,
last = :last,
initials = :initials,
clientname = :clientname,
phone_number = :phone_number,
email = :email
WHERE client.id = :id';
// All the same - value and var :=place holder
$s = $pdo->prepare($sql);
$s->bindValue(':first', $_POST['first']);
$s->bindValue(':id', $_POST['id']);
$s->bindValue(':clientname', $_POST['clientname']);
$s->bindValue(':last', $_POST['last']);
$s->bindValue(':initials', $_POST['initials']);
$s->bindValue(':phone_number', $_POST['phone_number']);
$s->bindValue(':email', $_POST['email']);
$s->execute();
Works fine if the clientid exists otherwise it updates the first entry in the table
The code posted by WolfShade is specific for tsql, the dialect of SQL used by Sybase and SQL server, and will not work in Mysql. In Mysql you would use their specific version of INSERT, i.e. INSERT … ON DUPLICATE. You could look for this in the Mysql documentation or do an Internet search for more details.
I have little MySQL experience… I’m mostly a T-SQL coder who has been learning PL-SQL for the last two years. 
I did some looking around and came across a SO forum post regarding this, and it implements precisely what @swampBoogie is talking about.
there are several threads about INSERT… ON DUPLICATE KEY UPDATE in this forum, too
unfortunately none of them actually show the working syntax after the initial question and replies
for your example, it would look like this –
INSERT
INTO client_phone
( clientid
, phone_number
, country_code )
VALUES
( 22
, 888
, 2 )
ON DUPLICATE KEY
UPDATE phone_number = VALUES(phone_number)
, country_code = VALUES(country_code)
@r937 ; I realy really did think you had it. First tried with id=10 and it failed on foreign key constraint - perfect.
Tried it with existing id not in this table - perfect.
Tried it with id that existed in the client_phone table it accepted BUT produced duplicate rows.
I am either being dense or not explaining myself. I am trying to update a record if it exists and, if it does not, I am trying to insert it.
Maybe I am missing something basic. It really does not seem that this should be difficult and yes I have spent HOURS reading the MySQL documentation. (The search function is great when you search for something it comes back in all the languages on gods’s earth!) I can find little useful info on EXISTS which I think I may need but cannot make work.
I can do all sorts of fixes for this specific instance but this will come up all over the place. Bottle of scotch (virtual) for right answewr.
Really think @r937 ; is almost there
Success (and bottle of virtual scotch on wayto R937 BUT… (see below!)
Read documentation on ON DUPLICATE KEY and realised that clientid, while a foreign key, was not specified as unique!
OK so now working for this table BUT…
I am not the most organised of beings and building a working alpha or a project so expect to make mistakes in record attributes.
Is there a way of finding out if a record exists and if it does, update it and if not insert?
I am sure I will want to do this and it may NOT be on fields marked as unique. There must be a realtively easy test does it exist etc…
Anyone???
the ON DUPLICATE KEY approach is by far the easiest way
if you have a situation where you don’t want duplicates on a certain column (or combination of columns), then just go ahead and declare it (or them) UNIQUE
the alternative is to break out and flex your START TRANSACTION … COMMIT/ROLLBACK chops
OK now totally flummoxed.
The clientid is unique but I want to update the record. But with the current solution I get and extra copy because we have told it to ignore the duplicate key.
Is there no way to test if a record a certain field-value exists? And then stinging that together with an IF statement and then either do UPDATE or INSERT. From a programming standpoint it seems incredibly basic but in MySQL it seems not to be doable - certainly not easily.
I have not looked at database rollbacks since 1979!!! I am just getting to grips with very basic MySQL and just trying to do a small (10 table) DB without (at this stage) any proper management tools (rollback transaction clash potection - there are only two users).
I really did thing EXISTS was the way to go.but I just could not make it work.
This would be ideal but I am ending up with multiple values for the foreign key which I cannot have (well I suppose I could and through in a timestamp element) and get rid of all but the latest but that is horribly inelegant.
Greatful for any idea if not will have to fudge this but seriously clinky coded.
Anyway thanks for your efforts.
OK hacked it in PHP.
$sql = "SELECT clientid FROM `client_phone` WHERE clientid=$the-client-id-we-want-to-check";
$s = $pdo->prepare($sql);
$s->execute();
$row = $s->fetch();
$clientid = $row['clientid'];
if ($clientid>0)
{
// Do UPDATE
} else
//Do INSERT
};
Not elegant but works like a charm.
OK after hours of getting things wrong this seems pretty robust:
$testsql = "SELECT clientid FROM `client_phone` WHERE clientid=$_POST[id] ";
$s = $pdo->prepare($testsql);
$s->execute();
$row = $s->fetch();
$clientid = $row['clientid'];
if ($clientid>0)
{
$sql =
"UPDATE client_phone SET
clientid=:clientid,
phone_number=:phone_number
WHERE clientid = $_POST[id]";
$s = $pdo->prepare($sql);
$s->bindValue(':phone_number', $_POST['phone_number']);
$s->bindValue(':clientid', $_POST['id']);
$s->execute();
}
else
{
$sql =
'INSERT INTO client_phone SET
clientid=:clientid,
phone_number=:phone_number';
$s = $pdo->prepare($sql);
$s->bindValue(':phone_number', $_POST['phone_number']);
$s->bindValue(':clientid', $_POST['id']);
$s->execute();
};
So I update the table that has clientid as a PRIMARY KEY then go on to update the other two tables with clientid as a unique FOREIGN KEY.
It is not very elegant but it works. My only worry is the
if ($clientid>0)
I thought MySQL sent back 0 if something like this was unset but when I tried
if ($clientid=0)
then it did not work, I am slightly flummoxed how a greater than zero test it working … but it is working.
I await a more robust and/or elegant solution.
Thanks for everyone’s help with this.
WHERE clientid = $_POST[id]";
needs to be changed to:
WHERE clientid = :clientid";
Thanks but nope.
The $_POST[id] is the id from the main client table that is the foreign key in this one. So I am checking to see if an entry exists for that specific user.
Sorry not at all clear but just wanted to share the results of all this.
HEY HEY HEY @SpacePhoenix ; just noticed you are from Poole I was born in Poole but the family lived in Parkstone…
Ah nostalgia. Poole - much nicer than Maui
hey, don’t include me in that, i never suggested you ignore the duplicate key
But <fx splutter> you said:
Actually I am 100% sure I did not explain myself clearly
But much more important than that is why testing for “= ZERO” fails but “>ZERO works”. Really got me confused. Not a problem just I like to understand what is going on.
Thanks for all you help with this and other questions.
You, sir, are a saint :good: