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.

Recommended Answers

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.

Jump to Post

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?

Jump to Post

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?

Jump to Post

All 12 Replies

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.

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

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?

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

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?

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

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?

That looked like it worked,

Query returned no resultset.

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

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

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.

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.