If input name found in SQL then do else do… with an SQL query

开发者_开发百科

Im trying to write an SQL query that will check in the table ‘persons’ in the column ‘rName’ for say a name “jack” and if it exists then UPDATE that row else INSERT a new row with the new rName.

I’v been trying out IF/ELSE statements but haven’t really seen how they work. Or is there a better way to do what I want without If/ELSE? (I haven’t done much of this SQL work, I used to only do UPDATE, INSERT, DELETE, ALTER… that kinda of stuff.

I have this so far: (if jack is found in the rName row then UPDATE it, else INSERT new row)

SELECT * FROM persons IF 'jack' == rName BEGIN [UPDATE Statement] END ELSE BEGIN [ INSERT Statement] END

Edit: I don’t think I quite explained what I wanted, rName should all be unique names, no 2 should be the same. I’v tried ON DUPLICATE KEY UPDATE, but it seems to just inserts a new row with the new data. But I want it to update the already existing rName (jack) row with the new data, the rName (jack) should stay the same but other fields in that row should be updated to the new data provided.

---

I see you're using mysql. You want mysql's insert ... on duplicate key update syntax. Documentation

---

In your case:

INSERT INTO persons(`rName`, `foo`) VALUES('jack', 'bar') ON DUPLICATE KEY UPDATE `foo` = VALUES(`bar`);

rName must have an unique key for this to work. If there is a 'jack' in the table, then it updates the field foo with 'bar' and if there is no 'jack', it inserts a new row with rName = 'jack' and foo = 'bar'.

---

The problem you are having with ON DUPLICATE KEY UPDATE is that your Name column doesn't have a unique constraint so by the table definition, you are allowing duplicates.

---

See also: http://dev.mysql.com/doc/refman/5.0/en/replace.html

You can do

REPLACE INTO persons(`rName`, `foo`) VALUES('jack', 'bar')

Although, technically, if a conflicting row already exists, it is deleted and replaced rather than updated. But this might be what you wanted anyway.