CODEWITHSUNDEEP
mysqlsql-update
Jasjeet Singh
Jasjeet Singh

Reputation: 332

how to update a field according to first entry of a user?

I have a table name devices.

+---------+---------------+---------------------+
| user_id | device_status | created_at          |
+---------+---------------+---------------------+
|       1 |             0 | 2017-03-10 10:39:41 |
|       1 |             0 | 2017-03-14 10:19:33 |
|       2 |             0 | 2017-03-14 10:57:59 |
|       1 |             0 | 2017-03-15 05:58:51 |
|       3 |             0 | 2017-03-15 06:01:32 |
|       3 |             0 | 2017-03-15 12:10:13 |
+---------+---------------+---------------------+

What i want to do is update device_status of a user_id which have stored first in mysql table.

for example we have 3 rows of user_id 1. with created_at date. i want to update device_status of user_id 1 created_at date 2017-03-10 10:39:41.

EDIT 1: the query i use 

UPDATE devices 
   SET device_status = 1 
 WHERE created_at = (SELECT created_at 
                       FROM (SELECT MIN(created_at) AS created_at 
                               FROM devices) AS t);

but this query only updated one record (2017-03-10 10:39:41). i want to update 

|       2 |             0 | 2017-03-14 10:57:59 |

and 

|       3 |             0 | 2017-03-15 06:01:32 |

records too.

Upvotes: 1

Views: 49

Answers (2)

RealCheeseLord
RealCheeseLord

Reputation: 795

If all you Dates are mutually exclusive you could just Group by user_id:

UPDATE devices 
   SET device_status = 1 
 WHERE created_at IN (SELECT created_at 
                        FROM (SELECT MIN(created_at) AS created_at 
                                FROM devices
                               GROUP BY user_id) AS t);

Upvotes: 2

Nakesh
Nakesh

Reputation: 576

Use min() function in subquery with where clause.

update devices set device_status=1 where user_id = 1 and 
created_at = (select min(created_at)  where user_id = 1);

Hope it solves your problem.

Upvotes: 0

Related Questions