mySQL: Knowing when to UPDATE and when to INSERT?

Question

Hello,

I'm still quite a mySQL newbie, so I would like to do this right to avoid future mishaps.

What is the right way to detect when do I need to UPDATE an entry in a table or INSERT a new one?

Thank you.

Answer 1

Well, this question is very incomplete. It's not question about database, but about your application logic. You should know when you create something new (adding user to your app), and when you change existing record (changing specific information about that user).

Maybe you should ask about your specific problem.

Answer 2

Check out the REPLACE INTO statement or the INSERT . . . ON DUPLICATE KEY syntax.

HTH,
Kent

Answer 3

If you want to insert a record if a value does not exist in a certain column, or update it when it exists, you should declare a UNIQUE constraint on the column and use this syntax:

INSERT
INTO    mytable (key, value)
VALUES  ($newkey, $newvalue)
ON DUPLICATE KEY UPDATE
SET     value = $newvalue