Creat Temporary Id in SQL Statement

Question

I have a table

id  name
1   Alpha
2   Alpha
3   Beta
4   Charlie
5   Charlie

I want to assign a temporary id at the run time in the SQL statement, So the desired result should be like

myid  name
1   Alpha
2   Beta
3   Charlie

So basically the logic should be like reading the value of name field and see if it is changed then increment the value for custom field. How we can achieve this?

RolandoMySQLDBA · Answer 1 · 2012-04-06T21:08:57.033

Create a temp table to build the myid column for you

DROP DATABASE IF EXISTS neeraj;
CREATE DATABASE neeraj;
USE neeraj
CREATE TABLE mytable
(
    id int not null auto_increment,
    myid int not null default 0,
    name varchar(20),
    primary key (id),
    key (name)
);
INSERT INTO mytable (name) VALUES
('Alpha'),('Alpha'),('Beta'),('Charlie'),('Charlie');
SELECT * from mytable;
CREATE TABLE mytablemyid LIKE mytable;
INSERT INTO mytablemyid (name) SELECT DISTINCT name FROM mytable;
UPDATE mytablemyid A
INNER JOIN mytable B
USING (name) SET B.myid = A.id;
DROP TABLE mytablemyid;
SELECT * from mytable;

Here is the result:

mysql> DROP DATABASE IF EXISTS neeraj;
Query OK, 2 rows affected (0.07 sec)

mysql> CREATE DATABASE neeraj;
Query OK, 1 row affected (0.00 sec)

mysql> USE neeraj
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id int not null auto_increment,
    ->     myid int not null default 0,
    ->     name varchar(20),
    ->     primary key (id),
    ->     key (name)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO mytable (name) VALUES
    -> ('Alpha'),('Alpha'),('Beta'),('Charlie'),('Charlie');
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * from mytable;
+----+------+---------+
| id | myid | name    |
+----+------+---------+
|  1 |    0 | Alpha   |
|  2 |    0 | Alpha   |
|  3 |    0 | Beta    |
|  4 |    0 | Charlie |
|  5 |    0 | Charlie |
+----+------+---------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE mytablemyid LIKE mytable;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO mytablemyid (name) SELECT DISTINCT name FROM mytable;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE mytablemyid A
    -> INNER JOIN mytable B
    -> USING (name) SET B.myid = A.id;
Query OK, 5 rows affected (0.05 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> DROP TABLE mytablemyid;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * from mytable;
+----+------+---------+
| id | myid | name    |
+----+------+---------+
|  1 |    1 | Alpha   |
|  2 |    1 | Alpha   |
|  3 |    2 | Beta    |
|  4 |    3 | Charlie |
|  5 |    3 | Charlie |
+----+------+---------+
5 rows in set (0.00 sec)

mysql>

Now you have a temporary column at your disposal

You could also build it as a separate table called mytablemyid

mysql> CREATE TABLE mytablemyid LIKE mytable;
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE mytablemyid DROp COLUMN myid;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO mytablemyid (name) SELECT DISTINCT name FROM mytable;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * from mytablemyid;
+----+---------+
| id | name    |
+----+---------+
|  1 | Alpha   |
|  2 | Beta    |
|  3 | Charlie |
+----+---------+
3 rows in set (0.00 sec)

mysql>

Creat Temporary Id in SQL Statement

1 Answers1