Re: Wierd INSERT ... SELECT syntax problem

2008-09-06 Thread Brent Baisley 
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]

Re: Wierd INSERT ... SELECT syntax problem

2008-09-06 Thread Dan Tappin 
Thanks for the tip. I am looking at just making 16 separate queries.  
It will be easier to manage and faster to run.


Dan

On Sep 6, 2008, at 9:37 PM, Brent Baisley [EMAIL PROTECTED] wrote:

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)


LOCDATA
-
A1
B2
C3
D4
E5
F6
...

and I am looking to run some sort of INSERT ... SELECT on this to  
make a new table like this:


LOCDATA
-
A010
A111
A212
A313
B020
B121
B222
B323
C030
C131
C232
C333
D040
D141
D242
D343
E050
E151
E252
E353
F060
F161
F262
F363

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]