CODEWITHSUNDEEP
mysql
Karyuu Ouji
Karyuu Ouji

Reputation: 314

Complex Update then Insert in SQL

I have spent many hours searching for a solution to my current problem, but with no luck.

I have 4 tables:

-quest

ACC_ID  key        value
10000   LOGIN      1
10000   LOGINCNT   3
10000   LOGINTMR   8465
10002   LOGIN      0
10002   LOGINCNT   2
10002   LOGINTMR   82415
10003   LOGIN      1
10003   LOGINCNT   1
10003   LOGINTMR   3650

-characters

*C_ID  ACC_ID  Online
100    10000   1
101    10000   0
102    10001   0
103    10000   0
104    10002   1
105    10003   1
106    10002   0
107    10000   0
108    10003   0

-mail

*M_ID  C_ID  Title    Message
1      100   Event    Free Gift
2      100   Event    Free Gift
3      104   Event    Free Gift
4      105   Event    Free Gift

-mail_item

*ID  M_ID  Item_ID  Amnt
1    1     3521     1
2    1     3522     1
3    3     3521     1
4    3     3522     1
5    2     1638     8
6    4     3521     1
7    4     3522     1

Note: column with * is unique to that table

what I want to do is UPDATE each value on quest table depending on these criteria..:

Notice that there are many rows on character with ACC_ID 10000 but only 1 row is online. Other ACC_ID might have no online character at all. So if a query is made on characters, it should return the row with online=1 or the last result if online=0.

Because of the complexity of this, it made my head spin in confusion. I hope someone could help.

Upvotes: 0

Views: 33

Answers (1)

Barmar
Barmar

Reputation: 780889

Here's the UPDATE query:

UPDATE quest AS q
LEFT JOIN (
    SELECT acc_id, MAX(online) AS online
    FROM characters
    GROUP BY acc_id) AS c
ON q.acc_id = c.acc_id
SET q.value =
    CASE q.key
        WHEN 'LOGIN' THEN IFNULL(c.online, 0)
        WHEN 'LOGINMTR' THEN IF(IFNULL(c.online, 0), UNIX_TIMESTAMP(), 0)
        ELSE q.value
    END

And the INSERT:

INSERT INTO mail (c_id, title, message)
SELECT c.c_id, 'Event', 'Free Gift'
FROM characters AS c
JOIN quest AS q ON c.acc_id = q.acc_id
WHERE q.key = 'LOGIN'
AND c.online = 1

I don't understand what should be inserted into mail_item.

Upvotes: 1

Related Questions