MySQL IF判断

[forget12327]

update user set balance =
if((select count(*) from user where flag = 1 )<500, balance + 1000/(500), balance + 1000/(select * from ( select count(*) from user where flag = 1 )a))
where flag = 1;

代码如上，我想实现数量小于500的话，就按照500进行计算，大于500就按照实际数进行计算，请问我写的SQL有问题吗？如果有问题的话希望能指出错误，谢谢！

[卖水果的net]

-- 完整语句
create table test(flag int, num int);
insert into test values(1,20),(1,25),(2,30);

select * from test;

-- 使用 greatest 函数就可以了。
update test
   set num = num + (select num from (select greatest(500, count(*)) num from test where flag = 1)x)
 where flag = 1;

select * from test;

drop table test;

[卖水果的net]

-- 使用 greatest 函数就可以了。
update user 
set balance  = (select  1000 + greatest(500, count(*)) from user where flag = 1)
where flag = 1

[shoppo0505]

(select * from ( select count(*) from user where flag = 1 )a) 这句什么意思？ 外面那个 select * 不用吧

[forget12327]

引用 1 楼 wmxcn2000 的回复:

-- 使用 greatest 函数就可以了。
update user 
set balance  = (select  1000 + greatest(500, count(*)) from user where flag = 1)
where flag = 1

运行这段代码时候数据库提示You can't specify target table 'user' for update in FROM clause 用的是PHPmyadmin

[forget12327]

引用 1 楼 wmxcn2000 的回复:

-- 使用 greatest 函数就可以了。
update user 
set balance  = (select  1000 + greatest(500, count(*)) from user where flag = 1)
where flag = 1

还有就是需要把之前的balance和计算得出的balance相加更新回去的。