Insert value from select within the same table

Question

table:message [id,username,userid,message,type,timediff,timestamp]

I have a table called message. When a message is started a new row is created, with a timestamp and timediff of null.

When the message is ended I would like to insert that into a row with the difference in time.

Eg Start 1,username,1234,message,start/s,null,000001 End: 2,username,1234,message,end/s,**20**,000021

INSERT into Messages.message (username,userid,message,type,timediff) 
values ("username","1234","Test","Ended/S",(
    select sum(unix_timestamp(CURRENT_TIMESTAMP())-unix_timestamp(timestamp))
    from messages
        WHERE userid = "1234" 
        AND type = "Started/S"
        Order by timestamp desc
        limit 1))

I also get an error, which I can't make sense of Error Code: 1093. You can't specify target table 'message' for update in FROM clause

Answer 1

It's preferable to use the INSERT ... SELECT syntax for a query like that. I think something like this should work:

insert into message (id, username, userid, message, type, timediff)
select id, username, userid, message, 'Ended/S', unix_timestamp() - unix_timestamp(tstamp)
from message m
where tstamp = (select MAX(tstamp) 
                from message m1 
                where m1.userid=1234 and m1.type='Started/S');

I've made a small demo on SQLFiddle where you can see it in operation.