INSERT statement question; Duplicates

[SubKamran]

I have a table, called "sessions" and it has a unique field, "session_id" (the user's session id).

Now, in my page, I want to create a INSERT statement something like this:

INSERT INTO sessions (session_id) VALUES(111452) WHERE "Row with the same session_id is NOT there"

In other words, don't insert it if it already exists. I can do this using TWO statements, a SELECT and an INSERT but I'd like to save effort if I possibly can...

Can I do this?

[redemption]

You can simply insert it and catch the error, if session_id is a primary key or unique column. Maybe if you can determine the actual error number for a duplicate insertion, you can use that in your server-side language to report that a duplicate insertion has been 'prevented' (if that's at all necessary).

[SubKamran]

But it would show the error, no? (this is supposed to be a "behind the scenes" insertion) :-P

[redemption]

You can suppress the error. What language are you using?

[SubKamran]

.net

[redemption]

Oops sorry I can't help with .NET since I've no real experience in it.

If you want to, I think it would be ok to post in the .NET forum to ask how to suppress error messages. Also, remember to post the specific language you're using (C#? VB? something else?)

If you're using C# though, I think you should be able to catch the exception and deal with it gracefully.

[Powerlord]

But it would show the error, no? (this is supposed to be a "behind the scenes" insertion) :-P

I could be wrong, but the IGNORE keyword may suppress this.

If you specify the keyword IGNORE in an INSERT with many value rows, any rows that duplicate an existing PRIMARY or UNIQUE key in the table are ignored and are not inserted.

Unfortunately, that "with many value rows" is confusing me.

[r937]

many value rows would presumably cover these two situations:

INSERT ... SELECT ... FROM where many rows are selected

INSERT ... VALUES (x...),(y...),(z...),... where many rows are inserted

if you IGNORE errors (i've not yet done this in mysql), you have no way of reporting back to the user that the insert tried to insert a dupe -- if that matters

[Powerlord]

if you IGNORE errors (i've not yet done this in mysql), you have no way of reporting back to the user that the insert tried to insert a dupe -- if that matters

...well, that's what SubKamran wanted:

But it would show the error, no? (this is supposed to be a "behind the scenes" insertion) :-P