检查记录是否存在，如果是，则“更新".如果不是，则“插入". [英] Check if record exists, if yes "update" if not "insert"

问题描述

我想检查表PREMIUM_SERVICE_USER是否存在任何记录 如果没有strClientID的记录插入到premium_service_user表中，则strClientID将timeValid更新为+30.

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.

我做错了什么?

它会增加timeValid 30天，但也会插入另一行.

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

推荐答案

您的问题是运行第一个if而不考虑@pre_var的值.

Your problem was running the first if without regard to the value of @pre_var.

这是一种稍微不同的方法，如果PREMIUM_SERVICE_USER很大，效率会稍高.

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

这篇关于检查记录是否存在，如果是，则“更新".如果不是，则“插入".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！