CODEWITHSUNDEEP
mysqlsql-update
Mostafa Abedi
Mostafa Abedi

Reputation: 541

Updating a column with time difference between current time and a record in same table

I want to update a column with time difference between current time and a record in same table.

Actually in this table column To should be NOW() and Diffs should be the difference between From and To:

| ID |       Froms      |    To    |     Diffs  |
+----+------------------+----------+------------+
| 1  |  15:15:59.00000  |   NULL   |     NULL   |
+----+------------------+----------+------------+

When I tried this query, I got a bunch of errors:

UPDATE dailyHours 
SET Diffs = (SELECT TIMEDIFF(TIME(NOW()), (SELECT Froms FROM dailyHours WHERE To is NULL)))
WHERE To IS NULL

Upvotes: 0

Views: 562

Answers (2)

Feras Al Sous
Feras Al Sous

Reputation: 1083

Assign NOW() to a variable then update the time with variable:

update_time=now()

now update like this:

UPDATE dailyHours SET Diffs = timed(update_time,Forms) WHERE `To` is NULL;

I hope to help you.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Why are you using a subquery?

If that is the logic you want, then:

update dailyHours
   set Diffs = timediff(time(now()), Froms)
   where `to` is null;

Upvotes: 1

Related Questions