CODEWITHSUNDEEP
mysqlsqlsql-updateinner-join
user2250333
user2250333

Reputation: 133

MySQL subtracting value from one table by some rows from second table

I have 2 tables players:

| ID | PLAYER | WARNINGS |
|----|--------|----------|
|  1 |      a |       35 |
|  2 |      b |       70 |
|  3 |      c |       65 |

warns:

| ID | PLAYER | POWER | ACTIVE |       TIMEEND |
|----|--------|-------|--------|---------------|
|  1 |      a |     5 |      1 | 1388051312120 |
|  2 |      a |    10 |      1 | 1388051312120 |
|  3 |      a |    20 |      1 | 1388051312120 |
|  4 |      b |    30 |      1 | 1388051312120 |
|  5 |      b |    40 |      1 | 1388051312120 |
|  6 |      c |    10 |      1 | 1388051312120 |
|  7 |      c |    55 |      1 | 1388051312120 |

And I want check every row from warns where warns.active=1, and then if warns.timeend <= NOW() remove (subtract) a warns.power from players.warnings of player and change warns.active=2

So after that tables must look like this:

players:

| ID | PLAYER | WARNINGS |
|----|--------|----------|
|  1 |      a |        0 |
|  2 |      b |        0 |
|  3 |      c |        0 |

warns:

| ID | PLAYER | POWER | ACTIVE |       TIMEEND |
|----|--------|-------|--------|---------------|
|  1 |      a |     5 |      2 | 1388051312120 |
|  2 |      a |    10 |      2 | 1388051312120 |
|  3 |      a |    20 |      2 | 1388051312120 |
|  4 |      b |    30 |      2 | 1388051312120 |
|  5 |      b |    40 |      2 | 1388051312120 |
|  6 |      c |    10 |      2 | 1388051312120 |
|  7 |      c |    55 |      2 | 1388051312120 |

I only have that: UPDATE players,warns SET warns.Active=2, players.Warnings=players.Warnings-warns.Power WHERE (warns.Active=1) AND (warns.TimeEnd <= NOW()) AND (warns.Player=players.Player); But that giving me:

players:

| ID | PLAYER | WARNINGS |
|----|--------|----------|
|  1 |      a |       30 |
|  2 |      b |       40 |
|  3 |      c |       55 |

warns:

| ID | PLAYER | POWER | ACTIVE |       TIMEEND |
|----|--------|-------|--------|---------------|
|  1 |      a |     5 |      2 | 1388051312120 |
|  2 |      a |    10 |      2 | 1388051312120 |
|  3 |      a |    20 |      2 | 1388051312120 |
|  4 |      b |    30 |      2 | 1388051312120 |
|  5 |      b |    40 |      2 | 1388051312120 |
|  6 |      c |    10 |      2 | 1388051312120 |
|  7 |      c |    55 |      2 | 1388051312120 |

So subtract only first row of every player... Any ideas how to fix that? And sorry for my bad English! I hope you understand me :)

Upvotes: 1

Views: 802

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

SQLite just updates a table at once.

BEGIN;
UPDATE players SET warnings=warnings - (SELECT SUM(power) FROM warns WHERE id=players.id AND DATETIME(??? TimeEnd ???) <= DATETIME('NOW') AND active=1);
UPDATE warns SET active=2 WHERE DATETIME(??? TimeEnd ???) <= DATETIME('NOW') AND active=1;
COMMIT;

What kind of TimeEnd is this?

However, I would suggest you an alternate approach (without redundant data):

TABLE: players (ID, Player);
TABLE: warns (ID, POWER, TIMEEND);
VIEW: active_warnings = SELECT ID, Player, COALESCE(SUM(Power),0)
                        FROM Player
                        LEFT JOIN warns USING(ID)
                        WHERE DATETIME(??? TimeEnd ???) <= DATETIME('NOW')
                        GROUP BY Id;

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

UPDATE players P 
INNER JOIN (SELECT W.PLAYER, SUM(W.POWER) WARNING
            FROM WARNS W WHERE W.active = 1 AND W.timeend <= NOW()
            GROUP BY W.PLAYER 
           ) A ON P.PLAYER = A.PLAYER
SET P.WARNINGS = P.WARNINGS - A.WARNING ;

UPDATE WARNS 
SET active = 2
WHERE active = 1 AND timeend <= NOW();

Upvotes: 2

Related Questions