I a table like this:
date time user data
131111 111111 Mike 23
131111 121212 Linda 12
131111 131323 Mike 45
I want to add a field called "userID", based on the existing data in the DB:
date time user userID data
131111 111111 Mike 1 23
131111 121212 Linda 2 12
131111 131323 Mike 1 45
As long as the userID is one-on-one mapping to an existing user, it would be fine.
It would be better if it starts from 1 to #ofUsers.
Is there a clean solution?
Try something like this:
First you add the new column:
ALTER TABLE tab1 ADD userid INT NOT NULL;
Then you do:
UPDATE tab1 t1
INNER JOIN (
SELECT a.user, @rownum := @rownum + 1 AS newID
FROM (
SELECT DISTINCT user
FROM tab1
ORDER BY user
) a
JOIN (
SELECT @rownum := 0
) r
) t2 ON t1.user = t2.user
SET t1.userid = t2.newID
The inner query, gets a unique id to each distinct user (starting at 1 and increasing alphabetically. You can remove the order by if you don't care about any order) in the table and updates your table accordingly.
Hope this helps.
ALTER TABLE mytable ADD userID INT;
UPDATE mytable, usertable
SET mytable.userID = usertable.ID
WHERE usertable.user=mytable.user;
