update a set a.point=case when b.regprize=26 and a.point-20>0 then a.point-20 when b.regprize=12 and a.point-6>0 then a.point-6 when b.regprize=26 and a.point-20<=0 and then 6 when b.regprize=12 and a.point-6<=0 and then 6 when b.regprize=6 then a.point-0 else a.point end from gameex a, userinfo b where a.userid=b.userid
UPDATE A SET point = CASE WHEN CASE WHEN B.regprize = 26 THEN A.point - 20 WHEN B.regprize = 12 THEN A.point - 6 END <= 0 THEN 6 ELSE CASE WHEN B.regprize = 26 THEN A.point - 20 WHEN B.regprize = 12 THEN A.point - 6 END END FROM gameex AS A JOIN userinfo AS B ON A.USDERID = B.USDERID WHERE B.regprize IN(26,12);
update a set a.point=case when (b.regprize=26) and (a.point>20) then a.point-20 when (b.regprize=26) and (a.point<=20) then 6 when (b.regprize=12) and (a.point>6) then a.point-6 when (b.regprize=12) and (a.point<=6) then 6 when (b.regprize<>26) and (b.regprize<>6) and (a.point<=0) then 6 else a.point end from gameex a, userinfo b where a.userid=b.userid
update a set a.point=case when (b.regprize=26) and (a.point>20) then a.point-20 when (b.regprize=26) and (a.point <=20) then 6 when (b.regprize=12) and (a.point>6) then a.point-6 when (b.regprize=12) and (a.point <=6) then 6 else a.point end from gameex a, userinfo b where a.userid=b.userid
SQL codeupdate aset a.point=casewhen b.regprize=26then a.point-20when b.regprize=12then a.point-6 else a.pointendfrom gameex a, userinfo bwhere a.userid=b.userid
create table ts (id int identity(1,1),num int) insert into ts select 15 union all select 8 union all select 3 union all select 6create table ts1 (id1 int identity(1,1),num1 int) insert into ts1 select 26 union all select 12 union all select 6 union all select 12select * from ts select * from ts1 declare @a int,@b int set @a=20 set @b=6 update ts set ts.num=ts.num-@a from ts,ts1 where ts.id=ts1.id1 and ts1.num1=26 update ts set ts.num=ts.num-@b from ts,ts1 where ts.id=ts1.id1 and ts1.num1=12select * from ts update ts set ts.num=@b from ts where num<=0select * from tsdrop table ts drop table ts1 ------------------- 表ts id num 1 15 2 8 3 3 4 6 表ts1 id1 num1 1 26 2 12 3 6 4 12 ---更新之后 表ts id num 1 -5 2 2 3 3 4 0 ---小于等于0的更新为6 表ts id num 1 6 2 2 3 3 4 6
update
a
set
a.point=case
when b.regprize=26 and a.point-20>0 then a.point-20
when b.regprize=12 and a.point-6>0 then a.point-6
when b.regprize=26 and a.point-20<=0 and then 6
when b.regprize=12 and a.point-6<=0 and then 6
when b.regprize=6 then a.point-0
else a.point
end
from
gameex a,
userinfo b
where
a.userid=b.userid
UPDATE A SET
point = CASE WHEN
CASE WHEN B.regprize = 26 THEN A.point - 20
WHEN B.regprize = 12 THEN A.point - 6
END <= 0 THEN 6
ELSE
CASE WHEN B.regprize = 26 THEN A.point - 20
WHEN B.regprize = 12 THEN A.point - 6
END
END
FROM gameex AS A
JOIN userinfo AS B
ON A.USDERID = B.USDERID
WHERE B.regprize IN(26,12);
a
set
a.point=case
when (b.regprize=26) and (a.point>20) then a.point-20
when (b.regprize=26) and (a.point<=20) then 6
when (b.regprize=12) and (a.point>6) then a.point-6
when (b.regprize=12) and (a.point<=6) then 6
when (b.regprize<>26) and (b.regprize<>6) and (a.point<=0) then 6
else a.point
end
from
gameex a,
userinfo b
where
a.userid=b.userid
a
set
a.point=case
when (b.regprize=26) and (a.point>20) then a.point-20
when (b.regprize=26) and (a.point <=20) then 6
when (b.regprize=12) and (a.point>6) then a.point-6
when (b.regprize=12) and (a.point <=6) then 6
else a.point
end
from
gameex a,
userinfo b
where
a.userid=b.userid
aset
a.point=casewhen b.regprize=26then a.point-20when b.regprize=12then a.point-6
else a.pointendfrom
gameex a,
userinfo bwhere
a.userid=b.userid
create table ts
(id int identity(1,1),num int)
insert into ts
select 15 union all
select 8 union all
select 3 union all
select 6create table ts1
(id1 int identity(1,1),num1 int)
insert into ts1
select 26 union all
select 12 union all
select 6 union all
select 12select * from ts
select * from ts1 declare @a int,@b int
set @a=20
set @b=6
update ts
set ts.num=ts.num-@a from ts,ts1 where ts.id=ts1.id1 and ts1.num1=26
update ts
set ts.num=ts.num-@b from ts,ts1 where ts.id=ts1.id1 and ts1.num1=12select * from ts update ts
set ts.num=@b from ts where num<=0select * from tsdrop table ts
drop table ts1
-------------------
表ts
id num
1 15
2 8
3 3
4 6
表ts1
id1 num1
1 26
2 12
3 6
4 12
---更新之后
表ts
id num
1 -5
2 2
3 3
4 0
---小于等于0的更新为6
表ts
id num
1 6
2 2
3 3
4 6