Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
That's not a temporary id; it's a false id. The effect is to appear to change the id number for Beta to the id number that actually "belongs" to Alpha. While it's possible to do that in a query, it's almost certainly not a solution to your problem. Instead of trying to do this, edit your question to describe the actual problem you're having. (Assigning a temporary id at run time is a solution you don't yet know how to implement. Tell us the actual problem, not the solution that doesn't work.) Related question by this user on SO. –  Mike Sherrill 'Cat Recall' Apr 6 '12 at 14:54
add comment

1 Answer

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>
share|improve this answer
    
Thanks for your answer Roland. I would like to know if that above solution can be produced without using temp table. –  neeraj Apr 8 '12 at 12:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.