检查记录是否存在，如果存在则“更新”，如果不存在则“插入”。

Question

我想要检查表PREMIUM_SERVICE_USER是否存在任何记录，如果存在以strClientID更新timeValid的值加30天，如果不存在则将其插入到premium_service_user表中。

我做错了什么？

它增加了timeValid的值30天，但也插入了另一行记录。

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

---

除了下面找到的修复方法，我还建议您将PREMIUM_SERVICE_USER的UPDATE语句合并为一条语句。 - Tom H

Answer 1

你的问题是在不考虑@pre_var的值的情况下运行了第一个if。

以下是一种稍微不同的方法，如果PREMIUM_SERVICE_USER很大，它会稍微更有效率。

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

- Donnie

Answer 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

- Rubi

Answer 3

你正在统计行数，但没有在决策中使用它。以下是一个可能有用的决策结构。

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

或者这个...

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

- Raj More

---

但如果计数返回1，那意味着记录存在于表中。我想要的是，如果行存在于表中，则进行更新。您的建议难道不是指如果计数返回0则进行更新吗？ - Ali Demirci

Answer 4

看起来你在第一个IF语句中检查了错误的变量。如果@Premium = 1，那么你会看到这种行为。

/* 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

- Robert Love