MySQL Best way to prevent duplicating records?

LambdaLambda · **Author**

Hey,

what's the best way to prevent duplicate insert mysql record in SourcePawn? In PHP I would use ON DUPLICATE KEY UPDATE, however, it won't work in SP (yes, I did try). Then I'm left with selecting the record first and if so include it? It's twicing the code, maybe there's any better way?

Dr. Greg House · *Last edited by Dr. Greg House; 12-03-2013 at 13:40.*

Define a primary key and use "INSERT OR UPDATE"?

Also are we talking SQL or SQLite (doublecheck)?

11530 · 12-04-2013 , 15:31 Re: MySQL Best way to prevent duplicating records?

I use "ON DUPLICATE KEY UPDATE" in one of my plugins. Don't remember it not working.

-Absolute- · 12-04-2013 , 15:48 Re: MySQL Best way to prevent duplicating records?

There is no difference between using mysql from php or source pawn. Make sure your tables are set up properly (having unique keys) or else it won't fire ON DUPLICATE

LambdaLambda · 12-04-2013 , 15:57 Re: MySQL Best way to prevent duplicating records?

K i get it.

Sry fot not answearing, I really couldn't. I'll take a look at it later on.

shavit · 12-05-2013 , 23:52 Re: MySQL Best way to prevent duplicating records?

I just tested and it works.

Code:

FormatEx(query, 256, "INSERT INTO playerpoints (auth, name, rank) VALUES ('%s', '%s', %d) ON DUPLICATE KEY UPDATE name = name;", auth, safeName, gI_Cache_ClientRank[client]);

No errors and no duplications.

friagram · 12-06-2013 , 07:44 Re: MySQL Best way to prevent duplicating records?

Or you could query, check the result rows. If there are, update, else insert...

-Absolute- · 12-06-2013 , 08:03 Re: MySQL Best way to prevent duplicating records?

Quote:

Originally Posted by shavit

I just tested and it works.

Code:

FormatEx(query, 256, "INSERT INTO playerpoints (auth, name, rank) VALUES ('%s', '%s', %d) ON DUPLICATE KEY UPDATE name = name;", auth, safeName, gI_Cache_ClientRank[client]);

No errors and no duplications.

This only works if your table's auth row is unique. Else it will just append since there was no duplicate.