Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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
like image 706
Ali Demirci Avatar asked Jul 06 '10 18:07

Ali Demirci


People also ask

How do you check if record already exist in SQL?

To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when row exists in the table, otherwise false is returned. True is represented in the form of 1 and false is represented as 0.

How do you find out if a record already exists in a database if it doesn't insert a new record?

You can either do this with a stored procedure or from ASP. SELECT 'This record already exists!' First, we check if the record exists with the EXISTS keyword. EXISTS executes the query we tell it to (the SELECT ) and returns a boolean value.

How do you know if the record exists before insert to avoid duplicates?

You want to look at the removeAll() method for Sets. First create a set of all the possible Ids you want to insert, then query the Contact object with records that already exist. Then you can use the removeAll method to remove all instance of the Id that already exist in Salesforce.


2 Answers

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
like image 131
Donnie Avatar answered Oct 02 '22 23:10

Donnie


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
like image 35
Rubi Avatar answered Oct 02 '22 23:10

Rubi