Posted on 4 Comments

Challenge: getting information out of INSERT/UPDATE

One annoying aspect of SQL is that sometimes you really want to get some result out of an INSERT or UPDATE statement. But in MySQL, you can. It’s just a nifty construct.

First just an UPDATE:

UPDATE tbl SET col = (@var := col), bla=value WHERE ...
SELECT @var

So, you can assign a column to itself (MySQL does a read before write, for updates), but also assign that value to a server-side variable which you can then retrieve. That’s how you get data out of a row you’re updating!

Now, how about the case where you need to insert something into one table, but regardless of whether or not it already existed you need to insert or update info into a second table (and also the id from the first table).
That’s your christmas challenge from me…. no selects or transactions necessary. Good luck!

Posted on 4 Comments

4 thoughts on “Challenge: getting information out of INSERT/UPDATE

  1. 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!

  2. 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

  3. 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 :).

  4. 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.

Comments are closed.