CODEWITHSUNDEEP
mysqlsql-update
lStoilov
lStoilov

Reputation: 1339

MySQL update values based on distinct value where a column value is the same

I have a DB with over 2+mil records where I have a table users with these columns

`id`      
`UserID`
`UserType`

What I want to achieve is to update UserType value to Returning if UserID already exists in a previous record.

like in this example:

 id | UserID  | UserType
-----------------------------
100 | 2258558 | New
101 | 9999999 | New
102 | 1111111 | New
103 | 2258558 | New
104 | 2258558 | New
105 | 2258558 | N/A

So, the query has to update row 103, 104, 105 to Returning as the UserID already exists on row 100.

That's fairly easy if I know

update users set UserType='Returning' where UserID='2258558' and id!='100'

The problem is that I will need to run that with thousands of unique UserID. So I was thinking if it is possible if somehow first distinct all unique values and then run it against the rest of rows, but I don't know how to do it.

Thanks

Upvotes: 1

Views: 96

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562861

You can use a join in an UPDATE. Match any row u2 to a row u1 that has the same user and is earlier. If such a row u1 exists, then u2 must not be the first one. So u2 may be updated.

UPDATE users AS u1 JOIN users AS u2 
  ON u1.UserID = u2.UserID AND u1.id < u2.id
SET u2.UserType = 'Returning';

Note I have not tested this, so please test it yourself on some sample data to make sure it does what you want, before you apply it to your real data.

Upvotes: 1

Related Questions