Check if record exists, if yes "update" if not "insert"
82,672
Solution 1
Your problem was running the first if
without regard to the value of @pre_var
.
This is a slightly different way of doing it which will be slightly more efficient if PREMIUM_SERVICE_USER
is large.
if @Premium = 1
begin
if exists(Select 1 From PREMIUM_SERVICE_USER Where strClientID = @strClientID)
BEGIN
update PREMIUM_SERVICE_USER set timevalid = timevalid+30 where strClientID = @strClientID
update PREMIUM_SERVICE_USER set bCurrent = 1 where strClientID = @strClientID
UPDATE TB_USER SET cash = cash+200 WHERE strAccountID = @strClientID
END
ELSE
BEGIN
INSERT INTO PREMIUM_SERVICE_USER (strClientID, timeReg, timeValid, bCurrent, durum) VALUES (@strClientID,getdate(),getdate() + 30,'1','1')
UPDATE TB_USER SET cash = cash+200 WHERE strAccountID = @strClientID
END
end
Solution 2
CREATE PROCEDURE sp_UpdateProcessed
AS
BEGIN
DECLARE @Processed_Status NVARCHAR(256)
SET @Processed_Status = 'ACTIVE'
IF(@Processed_Status <> 'Processed')
BEGIN
SET @Processed_Status = 'Active'
UPDATE ST_JnlMediumMoveNew
SET ST_JnlMediumMoveNew.Process_Status = @Processed_Status
END
END
Solution 3
You're counting the rows, but not using it in your decision making. Here is a decision structure that may be useful
Select @pre_var = count(*) From PREMIUM_SERVICE_USER Where strClientID = @strClientID
IF @pre_var = 0
BEGIN
/* Run Insert Code Here */
END
ELSE
BEGIN
/* Run Update Code Here */
END
IF @Premium = 1
BEGIN
/* Run Premier Members Update Code Here */
END
ELSE
BEGIN
/* Run Non-Premier Members Update Code Here */
END
Or this one..
IF @pre_var = 0
BEGIN
/* Run Insert Code Here */
END
ELSE
BEGIN
IF @Premium = 1
BEGIN
/* Run Premier Members Update Code Here */
END
ELSE
BEGIN
/* Run Non-Premier Members Update Code Here */
END
END
Author by
Ali Demirci
Updated on July 09, 2022Comments
-
Ali Demirci almost 2 years
I want to check table
PREMIUM_SERVICE_USER
if any records exists forstrClientID
updatetimeValid
for +30 if no records forstrClientID
insert topremium_service_user
table.What am I doing wrong?
It increases
timeValid
for +30 days but inserts another row too.SELECT @pre_var = count(*) FROM PREMIUM_SERVICE_USER WHERE strClientID = @strClientID /* bronze premium - 200 cash */ IF @Premium = 1 BEGIN INSERT INTO PREMIUM_SERVICE_USER (strClientID, timeReg, timeValid, bCurrent, durum) VALUES (@strClientID,getdate(),getdate() + 30,'1','1') UPDATE TB_USER SET cash = cash+200 WHERE strAccountID = @strClientID END IF @Premium = 1 AND @pre_var = 1 BEGIN UPDATE PREMIUM_SERVICE_USER SET timevalid = timevalid+30 where strClientID = @strClientID UPDATE PREMIUM_SERVICE_USER SET bCurrent = 1 where strClientID = @strClientID UPDATE TB_USER SET cash = cash+200 WHERE strAccountID = @strClientID END
-
Ali Demirci almost 14 yearsbut if count returns me 1 that means record exist on table. i want to do is if row exist on table do update. doesnt ur suggestion mean if count returns 0 do update?