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
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.
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.
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.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With