Well, for your simple example, you can use query variables to add the
"counters".
SET @cntr:=0, @lastVal:='A'
INSERT INTO tableB LOC,DATA SELECT CONCAT(LOC,
CONCAT( IF(@lastVal=LOC, @cntr:[EMAIL PROTECTED], @cntr:=0),
IF(@lastVal:=LOC,'',''))) LOC, CONCAT(DATA, @cntr) FROM tableA ORDER
BY LOC
That should add a sequential number to LOC and DATA that will reset to
0 whenever the value of LOC changes. Some of the IFs in there are just
to suppress output of variable assignment.
Hope that helps
Brent Baisley
On Sep 5, 2008, at 5:44 PM, Dan Tappin wrote:
I have an existing data set - here is an example (the real one is
more complex than this)
LOC DATA
-------------
A 1
B 2
C 3
D 4
E 5
F 6
...
and I am looking to run some sort of INSERT ... SELECT on this to
make a new table like this:
LOC DATA
-------------
A0 10
A1 11
A2 12
A3 13
B0 20
B1 21
B2 22
B3 23
C0 30
C1 31
C2 32
C3 33
D0 40
D1 41
D2 42
D3 43
E0 50
E1 51
E2 52
E3 53
F0 60
F1 61
F2 62
F3 63
I basically want to take the data from each row, perform n number of
operations on it and insert it into a new table. I could make a PHP
script that does this but I figured there had to be a better way.
Any ideas?
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
