2

I have a table called temp_rfm where col1 is effectively the customer id (I had an illegal mix of coalitions problem) and the calc_date which is increasing beginnings of months. 

SELECT * FROM temp_rfm ; 

col1 | calc_date  
-----|-----------
20   | 2016-02-01    
20   | 2016_03_01     
20   | 2016-04-01    
21   | 2016-01-01     
21   | 2016-02-01     
21   | 2016_03_01     
21   | 2016-04-01     
21   | 2016-05-01     
42   | 2017_06_01     
42   | 2017-07-01  
42   | 2017-08-01

What I am wanting is to input a column into temp_rfm table to increment each date for each customer, as below: 

col1 | calc_date | row_number
-----|-----------|----------- 
20   | 2016-02-01|     1
20   | 2016_03_01|     2
20   | 2016-04-01|     3
21   | 2016-01-01|     1
21   | 2016-02-01|     2
21   | 2016_03_01|     3
21   | 2016-04-01|     4
21   | 2016-05-01|     5
42   | 2017_06_01|     1
42   | 2017-07-01|     2
42   | 2017-08-01|     3

I was successful in doing this with a simple select statement using user defined variables. But if I want to make it permanent in a table (update table or insert into or set) it doesn't work.

Here are the queries I have tried and their results:

Getting rid of illegal mix of coalitions problem

UPDATE temp_rfm t inner join 
(SELECT customer_id, customer_id COLLATE utf8_unicode_ci as col2 
from temp_rfm) T
USING (customer_id) set t.col2=T.col2;

Inputting the incrementing numbers into column called row_number

set  @col2 = 1, @num :=1;

Update temp_rfm t inner join 
(select  col2,
@num := if (@col2 = col2, @num + 1, 1) as row_number,
@col2 := col2 as dummy
from temp_rfm
order by col2, calc_date ) T
USING (col2) set t.row_number = T.row_number and t.dummy = T.dummy ;

Then when I do the query SELECT col1, calc_date, row_number, dummy FROM temp_rfm ; 

col1 | calc_date | row_number| dummy 
-----|-----------|-----------|------ 
20   | 2016-02-01|     1     |  20
20   | 2016_03_01|     1     |  20
20   | 2016-04-01|     1     |  20 
21   | 2016-01-01|     1     |  21
21   | 2016-02-01|     1     |  21
21   | 2016_03_01|     1     |  21
21   | 2016-04-01|     1     |  21
21   | 2016-05-01|     1     |  21
42   | 2017_06_01|     1     |  42
42   | 2017-07-01|     1     |  42
42   | 2017-08-01|     1     |  42

I have also tried the following queries and I get similar results! 

set @customer_id_chg = 1  , @num=1; 

INSERT INTO temp_rfm  (row_number, dummy)
(select 
@num := if ((@col2 =col2), @num + 1, 1) as row_number,
@col2 := col2 as dummy
from temp_rfm
order by col2, calc_date);

set @customer_id_chg = 1  , @num=1; 

select customer_id_chg, transaction_date, value_realised, items_ordered, date_diff, 
@num := if (@customer_id_chg = customer_id_chg, @num + 1, 1) as row_number,
@customer_id_chg:= customer_id_chg as dummy
from transactions
where customer_id = 10073
order by customer_id_chg, transaction_date;

Any help would be greatly appreciated ! I am working on MySQL workbench.

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181
Robyn Turner
  • 21
  • 2

1 Answers1

0

Untested code:

update temp_rfm t
set t.dummy = (select ifnull(max(dummy), 0) + 1 from temp_rfm);

Let me know if I am wrong when proposing this. The reasoning is that whenever we want to set a new dummy, we find the previously known maximum value (defaulting to 0) and add 1 to it.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Sorry sent message yesterday-- dont know if it went through but general summary. – Robyn Turner Oct 05 '17 at 07:28
  • 1) I am a novice so please bare with me, 2) MySQL workbench doesn't allow for select from in a update set statement (error says you cant specify target table t for update in from clause) 3) How would I implement this query, would I get rid of the dummy variable in the main query? – Robyn Turner Oct 05 '17 at 07:31
  • @RobynTurner if this is not allowed, then you can select all rows using a cursor, iterate the cursor and update separately each row using a helper variable, which will be increased in each iteration. – Lajos Arpad Oct 06 '17 at 06:58