Update last row in table

[muppet]

I have a table that a new line gets added to when the machine stops with reason for stopping, when the machine gets going again I want to add the time lost.

So when the machine stops the table will look like this:

ID | reason        | time_lost
1  | short circuit | 1:23
2  | rainy day     | 2:54
3  | short circuit | 2:12
4  | lunch

|

Then when they start it again after lunch it will look like this

ID | reason        | time_lost
1  | short circuit | 1:23
2  | rainy day     | 2:54
3  | short circuit | 2:12
4  | lunch         | 28:34

How do I insert time_lost into he last entry?

I'm not worried about how to calculate the lost time, I've done that. Just need to insert it in the right place.

[Ezzaral]

Perhaps something like

update yourTable set time_lost=theValue where ID=(select max(ID) from yourTable) and time_lost is null

I don't work with MySQL specifically so there may be a slight syntax variation.

[muppet]

Well that was pretty much what I was thinking too. Although that kicks an error:

You can't specify target table 'abm_downtime' for update in FROM clause

[Ezzaral]

You can't sub-select from the table you are updating? Did you check your parenthesis to make sure it didn't just parse it incorrectly?

[muppet]

hmm, you confused me a little there.

Heres my query

UPDATE  abm_downtime set
lost_time = now()
WHERE id = (select max(id) from abm_downtime)and lost_time = null

[Ezzaral]

Yes, I found this buried in the manual

Currently, you cannot update a table and select from the same table in a subquery.

Very inconvenient.

Can you select the ID in a separate statement and then run your update?

[muppet]

Looks like I'll have to. Thanks a lot for your help.

[Ezzaral]

In the comments on UPDATE, one guy mentions using the following work-around

UPDATE  abm_downtime set
lost_time = now()
where lost_time = null
order by ID desc limit 1

You could give that a try I suppose.

I'm assuming you aren't actually using now() for lost_time, right?

[muppet]

That looked like it worked,

Query returned no resultset.

But no change in the table, and yes the now() is just a temporary placeholder.

[Ezzaral]

Use WHERE lost_time is NULL instead of = NULL Sorry, I should have changed that when I copied your previous code to alter it.

[muppet]

if I take out the

WHERE lost_time = NULL

it works. So its close, will report back when I find the cure. thanks again for your help.

[muppet]

Use WHERE lost_time is NULL instead of = NULL Sorry, I should have changed that when I copied your previous code to alter it.

thats it. thanks

[Ezzaral]

See my previous post, which I think you may have cross-posted with.