Re: Of Mysql, DBI and PK best practices

Howdy!

The alternative is to first select the PK from the table. If you get a result, then you do the update, otherwise do the insert. That means that errors will be real errors. That means you always execute two statements, but your normal condition is that there are no errors.

yours,
Michael

Comment on Re: Of Mysql, DBI and PK best practices

Replies are listed 'Best First'.
Re^2: Of Mysql, DBI and PK best practices by kyle (Abbot) on Apr 17, 2007 at 14:34 UTC
If there's only ever one client, that's fine. Otherwise, this contains a race condition that the OP does not have. Imagine two clients select, both see nothing, both try to insert, one fails. At that point, the one that failed to insert should update, exactly like what the OP is already doing (but without the select). Update: It also occurs to me that if you're not concerned about a race condition, you could do something like this without the select. First try to update, then check to see how many rows were affected. If there weren't any, do the insert. As I say, this has a race condition also, but it will sometimes be one statement instead of always being two.	[reply]

Replies are listed 'Best First'.

Re^2: Of Mysql, DBI and PK best practices
by kyle (Abbot) on Apr 17, 2007 at 14:34 UTC

If there's only ever one client, that's fine. Otherwise, this contains a race condition that the OP does not have. Imagine two clients select, both see nothing, both try to insert, one fails. At that point, the one that failed to insert should update, exactly like what the OP is already doing (but without the select).

Update: It also occurs to me that if you're not concerned about a race condition, you could do something like this without the select. First try to update, then check to see how many rows were affected. If there weren't any, do the insert. As I say, this has a race condition also, but it will sometimes be one statement instead of always being two.

[reply]