DECLARE @CheckMsgnr INT=0
UPDATE M SET
M.MSGNR=CASE @CheckMsgnr WHEN 0 THEN M.MSGNR ELSE T.MSGNR END,
M.MatchActive=T.MatchActive,
M.MatchStatus=T.MatchStatus,
M.BetStatus=T.BetStatus,
M.MatchScore=T.MatchScore
FROM MATCHES M JOIN TMP_MATCHES T ON M.MatchID=T.MatchID
WHERE (@CheckMsgnr=0 OR (@CheckMsgnr=1 AND M.MSGNR<=T.MSGNR))
想了一下 不知道这样处理对不对
UPDATE MATCHES M
SET
(
M."MSGNR",
M."MatchActive",
M."MatchStatus",
M."BetStatus",
M."MatchScore",
M."UpdateTS"
)
=
(
SELECT
CASE P_CHECKMSGNR WHEN 0 THEN M."MSGNR" ELSE T."MSGNR" END ,
T."MATCH_ACTIVE",
T."MATCH_STATUS",
T."BET_STATUS",
T."SCORE",
P_NOW
FROM TEMP_UPDATEMATCHSTATUS T
WHERE
M."MatchID"=T."MATCHID"
AND (P_CHECKMSGNR=0 or (P_CHECKMSGNR=1 AND M."MSGNR"<T."MSGNR"))
);
------解决思路----------------------
DECLARE
CheckMsgnr pls_integer := 0;
begin
if CheckMsgnr=0 then
UPDATE MATCHES M SET (MSGNR,MatchActive,MatchStatus,BetStatus,MatchScore) = (
select case CheckMsgnr when 0 then M.MSGNR ELSE T.MSGNR END,
T.MatchActive,
T.MatchStatus,
T.BetStatus,
T.MatchScore
where M.MatchID=T.MatchID
)
where exists(select null from TMP_MATCHES T where M.MatchID=T.MatchID)
elsif CheckMsgnr = 1 then
UPDATE MATCHES M SET (MSGNR,MatchActive,MatchStatus,BetStatus,MatchScore) = (
select case CheckMsgnr when 0 then M.MSGNR ELSE T.MSGNR END,
T.MatchActive,
T.MatchStatus,
T.BetStatus,
T.MatchScore
where M.MatchID=T.MatchID AND M.MSGNR<=T.MSGNR
)
where exists(select null from TMP_MATCHES T where M.MatchID=T.MatchID AND M.MSGNR<=T.MSGNR)
ELSE
null;
end if;
end;
/