Check if record exists, if yes "update" if not "insert"

Question

I want to check table PREMIUM_SERVICE_USER if any records exists for strClientID update timeValid for +30 if no records for strClientID insert to premium_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

In addition to the fix found below, I would also suggest that you combine the UPDATE statements into a single statement for PREMIUM_SERVICE_USER — Tom H, Jul 06 '10 at 19:58

score 21 · Accepted Answer · answered Jul 06 '10 at 18:57

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

score 5 · Answer 2 · edited Nov 25 '11 at 07:45

5

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

edited Nov 25 '11 at 07:45

Mark Hall

53,938
9
94
111

answered Nov 25 '11 at 07:03

Rubi

51
1
1

Raj More · Answer 3 · 2010-07-06T19:04:42.220

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

but 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? — Ali Demirci, Jul 06 '10 at 18:58

score 0 · Answer 4 · answered Jul 06 '10 at 19:00

Looks like you are checking the wrong variable in the first IF Statement. If @Premium = 1 then you will see this behavior.

/* bronze premium - 200 cash */
IF @pre_var = 0
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

Check if record exists, if yes "update" if not "insert"

4 Answers4

Linked