User:	gmaxia
Date:	2006-12-24 10:33 (UTC)
Subject:	first it comes to mind

Given this table, with the following contents

create table t1 (col1 int not null auto_increment primary key, col2 char(10));
INSERT INTO t1 values (1,'abc'), (2, 'def'), (3, 'ghi');
select * from t1;
+------+------+
| col1 | col2 |
+------+------+
|    1 | abc  |
|    2 | def  |
|    3 | ghi  |
+------+------+

Here is an INSERT statement that will also record what it is inserted:

insert into t1 
    col2= @var2 := coalesce(col2,'xxx') 
on duplicate key update col2 = @var2;

select @var1 := last_insert_id()as last_id , @var2;
+---------+-------+
| last_id | @var2 |
+---------+-------+
|       4 | xxx   |
+---------+-------+

insert into t1 
    set col1= @var1, 
    col2= @var2 := coalesce(col2,'yyy') 
on duplicate key update col2 = @var2;

select @var1, @var2;
+-------+-------+
| @var1 | @var2 |
+-------+-------+
| 4     | zzz   |
+-------+-------+

Merry Christmas!

Reply | Thread | Link

User:	(Anonymous)
Date:	2006-12-27 08:58 (UTC)
Subject:	Re: first it comes to mind

I think we have at least one more possibility.

Assuming we have a table with an id-column (auto_increment, primary key) and one column called "Name", which is indexed by a unique key.

1)

 INSERT INTO names
  ( Name ) VALUES ( "John" )
ON DUPLICATE KEY
  UPDATE
    id=LAST_INSERT_ID(id),
    Name = "John";

2)

SELECT LAST_INSERT_ID()

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+

Greetings,
Anse

Reply | Parent | Thread | Link

User:	(Anonymous)
Date:	2006-12-27 17:58 (UTC)
Subject:	Re: first it comes to mind

Well, 'RETURNING' clause has been used for years on real RDBMS.

Take a look at it; MySQL newbies are always excited about 'reinventing' the wheel every week :).

Reply | Parent | Thread | Link

User: arjen_lentz Date: 2006-12-29 01:51 (UTC) Subject: Re: first it comes to mind

No need to bitch, it says more about you and I'm sure that's not what you intended.
We can all exchange ideas and debate like good grownups.

The RETURNING clause on its own actually does not accomplish the functionality required by the question. What RETURNING does is return the information that was actually inserted, it does not deal with any information should the row already exist.

Reply | Parent | Thread | Link