mysql>  create procedure tax_wages(in s_id smallint,in s_wages smallint)
      ->  begin
      ->  declare c int;
      ->  declare tax int;
      ->  set c=s_wages/1000;
      ->  case c
      ->  when 0 then update tax_table set tax=0,wages=s_wages
      ->  where id=s_id;
      ->  when 1 then update tax_table set tax=(s_wages-1000)*0.02,wages=s_wages
      ->  where id=s_id;
      ->  when 2 then update tax_table set tax=(s_wages-2000)*0.04 20,wages=s_wage
      ->
      ->  where id=s_id;
      ->  else update tax_table set tax=(s_wages-3000)*0.1 60,wages=s_wages
      ->  where id=s_id;
      ->  end case;
      ->  end;//
Query OK, 0 rows affected (0.00 sec)
mysql> call tax_wages(1,2500);//
Query OK, 1 row affected (0.00 sec)
mysql> select * from tax_table;//
---- ------ ------- ------
| id | name | wages | tax  |
---- ------ ------- ------
|  1 | 陈一 |  2500 |    10 |
|  2 | 林二 |        0 |      0 |
|  3 | 张三 |        0 |      0 |
|  4 | 李四 |        0 |      0 |
|  5 | 王五 |        0 |      0 |
---- ------ ------- ------
5 rows in set (0.00 sec)
mysql> drop procedure tax_wages;//
Query OK, 0 rows affected (0.00 sec)
mysql>  create procedure tax_wages(in s_id smallint,in s_wages smallint)
      ->  begin
      ->  declare c int;
      ->  set c=floor(s_wages/1000);
      ->  case c
      ->  when 0 then update tax_table set tax=0,wages=s_wages
      ->  where id=s_id;
      ->  when 1 then update tax_table set tax=(s_wages-1000)*0.02,wages=s_wages
      ->  where id=s_id;
      ->  when 2 then update tax_table set tax=(s_wages-2000)*0.04 20,wages=s_wage
      ->  where id=s_id;
      ->  else update tax_table set tax=(s_wages-3000)*0.1 60,wages=s_wages
      ->  where id=s_id;
      ->  end case;
      ->  end;//
Query OK, 0 rows affected (0.00 sec)
mysql> call tax_wages(1,1500);//
Query OK, 1 row affected (0.00 sec)
mysql> select * from tax_table;//
---- ------ ------- ------
| id | name | wages | tax  |
---- ------ ------- ------
|  1 | 陈一 |  1500 |    10 |
|  2 | 林二 |        0 |      0 |
|  3 | 张三 |        0 |      0 |
|  4 | 李四 |        0 |      0 |
|  5 | 王五 |        0 |      0 |
---- ------ ------- ------
5 rows in set (0.01 sec)
mysql> call tax_wages(2,3000);//
Query OK, 1 row affected (0.00 sec)
mysql> call tax_wages(3,1000);//
Query OK, 1 row affected (0.02 sec)
mysql> call tax_wages(4,5000);//
Query OK, 1 row affected (0.00 sec)
mysql> select * from tax_table;//
---- ------ ------- ------
| id | name | wages | tax  |
---- ------ ------- ------
|  1 | 陈一 |  1500 |    10 |
|  2 | 林二 |  3000 |    60 |
|  3 | 张三 |  1000 |      0 |
|  4 | 李四 |  5000 |  260 |
|  5 | 王五 |        0 |      0 |
---- ------ ------- ------
5 rows in set (0.00 sec)

MySQL服务器update set **=(select)的使用方法

在mysql中，不能直接使用set select的结果，必须使用inner join：update a inner join (select yy from b) c on a.id =b.idset a.xx = c.yyupdatea inner join

MySQL