MySQL当条件成立时，插入数据（多条插入）

给今天过生日的用户发放50枚

INSERT INTO coin_trade (coin_amount, subject, word, user_id, trade_time, goods_id)
  SELECT#插入的每条数据来自于这个select查到的二维数组
    '50',
    (SELECT goods_name
     FROM goods
     WHERE goods_id = 19
     LIMIT 1),
    'Happy Birthday!',
    user_id,
    now(),
    19
  FROM users
  WHERE
    date_format(birthday, '%m-%d') = date_format(now(), '%m-%d');#是否是今天过生日的用户

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
 

完整的程序,保存下来，日后查看

#1.给当天过生日的用户发放50枚图灵币
UPDATE users
SET turing_coin = turing_coin + (SELECT constant_value
                                 FROM constant
                                 WHERE constant_name = 'BIRTHDAY_COIN'
                                 LIMIT 1)
WHERE date_format(birthday, '%m-%d') = date_format(now(), '%m-%d');

#2.给当天过生日的用户发放图灵币50枚插入记录
INSERT INTO coin_trade (coin_amount, subject, word, user_id, trade_time, goods_id)
  SELECT
    -(SELECT constant_value
      FROM constant
      WHERE constant_name = 'BIRTHDAY_COIN'
      LIMIT 1),
    (SELECT goods_name
     FROM goods
     WHERE goods_id = 19
     LIMIT 1),
    'Happy Birthday!',
    user_id,
    now(),
    19
  FROM users
  WHERE
    date_format(birthday, '%m-%d') = date_format(now(), '%m-%d');

#3.更新goods_id=19的记录
UPDATE goods
SET goods_number     = (goods_number -
                        (SELECT count(1)
                         FROM users
                         WHERE
                           date_format(birthday, '%m-%d') = date_format(now(), '%m-%d')
                        )),
  goods_trade_number = (goods_trade_number +
                        (SELECT count(1)
                         FROM users
                         WHERE
                           date_format(birthday, '%m-%d') = date_format(now(), '%m-%d')
                        ))
WHERE goods_id = 19;


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
  
34
  
35
  
36
  
37
  
38
  
39
  
40
  
41
  
42
  
43
 

文章来源: blog.csdn.net，作者：爱玲姐姐，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/jal517486222/article/details/84648851