select and insert in one command

Anon

is there anyay, for MySQL to combine this into one statment. Seems silly to have extra statment to check if the user exists already.

// check to make see if the user id exists already
$qry = \"select from table id where username=\'$username\'\";
$mysql->query($qry);

if (!mysql_affeted_rows) {
$qry = \"insert into table whatever . .\"
$mysql->query($qry);
}

Anon

No there isn't, but the way you're doing it is not
recommended, either: it allows more than one client
to (attemt to) insert the same key values.

The correct way to do this is make sure the
database is enforcing a uniqueness constraint
(i.e. primary key) and then attempt the insertion
first.

What happens with the check-first method is
either (1) you have to check to see if the
insert fails anyway, if you do correctly have
the primary/unique constraint set up, or (2)
if you don't then your script can indeed insert
multiple records with the same "key" values.

Anon

My usual soltion to this problem is to make two queries within a single transaction.

I'm not sure if mySQL has the ability to define transactions spanning multiple queries. I do it all the time working with MS SQL Server through ODBC, by calling odbc_autocommit(false) and then calling odbc_commit() when the transaction finishes successfully, or odbc_rollback() if something fails.

Hope This helps
Yaron

Anon

My take on that is you are just lucky. What you describe is
the opposite situation from updating, where you do indeed
want to do SELECT ... FOR UPDATE followed by UPDATE inside
a single transaction in many cases. But in the insert-if-not-
found scenario, there isn't any row to be locked in the
not-found case. Are you saying when you start a transaction
and do a SELECT that fails to return any rows, that SQL
Server and/or the ODBC driver places a write lock on the
entire table? Because that's what it would take to
prevent any other client from doing the same set of
queries with the same proposed unique key value
at the same time from interfering with yours.

It's just statistically unlikely; that's why I say it's
a matter of luck.

Anon

it's probably worthwhile to check out mySQL REPLACE INTO syntax. it inserts or "updates," as appropriate. maybe that will suffice for your needs. see http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#REPLACE

Anon

Arthur,

Replace is not the same as SELECT...INSERT it is the same as SELECT...UPDATE.

Galois,

MySQL does support INSERT/SELECT statements along this line:

insert into table_a (field1,field2,field3) select fielda,fieldb,fieldc from table_b where some_condition;

This also supports multiple rows. If the where condition results in multiple rows then there will be an equal number of inserts.

Anon

i'm not sure i understand what you mean, rod. the manual says the following:

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a unique index, the old record is deleted before the new record is inserted.

Anon

As far as I know about transactions, that's what they are for: to ensure serializability of events, i.e. that if two transactions from different connections try to update the same resource at the same time, the result will be as if they arrived separately, the second coming in after the first has finished executing. The way the DBMS deals with this may differ: it may issue a lock on the whole table, or it may try to let both transactions proceed until there's a conflict and then rollback one of them, and so on.

The DBMS may also give you the option of selecting concurrency level. I know Sybase has such an option, and in MS SQL Server you can do this through
SET TRANSACTION ISOLATION LEVEL

Again, I am not sure how much control over this there is in mySQL.

Yaron

Anon

true, but that's not what Galois requested.

Anon

don't think I ever got an answer to my question. Got a bunch of other answers, but . .

Anon

Sure you got an answer; it just wasn't the one you wanted:

No. (And, don't do it that way, either).