0

I want to update repetitive columns, And I build virtual column for did this. I can "select" but can't "update"

Here is SQL Fiddle :

http://sqlfiddle.com/#!9/d09189/3

Also Code review :

I have this table :

CREATE TABLE game_log (
    user_id int,
    game_id int,
    day_number int,
    game_detail int,
    wallp_det int );

INSERT INTO game_log (user_id, game_id, day_number, game_detail, wallp_det)
VALUES (11,21, 1,12,14),           
       (11,21, 2,18,24),
       (11,22, 3,20, 1),
       (11,22, 4,25,30),
       (11,23, 5, 5, 2),
       (12,21, 6,25,10),
       (12,22, 7,12,22),
       (12,22, 8,23, 2),
       (12,23, 9, 4,12),
       (13,21,10, 9, 8),
       (13,21,11,28,38);

I can "select" but can't "update" :

SET @s := 0;
select  sum(game_detail)  As ga_datail, user_id,game_id, @s := @s + 1 As day_num
from game_log 
group by user_id,game_id;

After this, I will delete repetitive rows

what I have :

   user_id  | game_id |  day_number | game_detail |   wallp_det
    ------  |---------|-------------|-------------|--------
    11      | 21      |  1          | 12          |  14
    11      | 23      |  2          | 18          |  24
    11      | 22      |  3          | 20          |  1
    11      | 23      |  4          | 25          |  30
    11      | 23      |  5          | 5           |  2
    12      | 21      |  6          | 25          |  10
    12      | 22      |  7          | 12          |  22
    12      | 23      |  8          | 23          |  2
    12      | 23      |  9          | 4           |  12
    13      | 21      |  10         | 9           |  8
    13      | 21      |  11         | 28          |  38

- what i want :

   user_id  | game_id |  day_number | game_detail |   wallp_det
    ------  |---------|-------------|-------------|--------
    11      | 21      |  1          | 12          |  14
    11      | 23      |  0          | 48          |  56
    11      | 22      |  3          | 20          |  1
    12      | 21      |  6          | 25          |  10
    12      | 22      |  7          | 12          |  22
    12      | 23      |  0          | 27          |  14
    13      | 21      |  0          | 37          |  46

Thanks in advance.

bumped to the homepage by Community yesterday

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

  • 4
    You've tagged mysql twice and SQL Server twice. These are very different products and syntax may vary widely. Can you please pick an RDBMS? – Aaron Bertrand Nov 15 '18 at 16:17
  • What determines that a row is repeated? The example is not helpful since the "want" includes some data not in the source. (Eg, wallp_det=46) – Rick James Nov 16 '18 at 0:00
  • I want to update repetitive columns There is NO duplicates both in source data and in intermediate recordset. And this intermediate result do not match the query which you show. – Akina Nov 16 '18 at 5:26
0

I solved it this way :

INSERT INTO game_log (game_detail,user_id,game_id,day_number)
select  sum(game_detail) As game_datail,user_id,game_id,0   from game_log group by user_id,game_id

--

DELETE FROM game_log where day_number != 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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