asked on

Need to reporgam in such a way that I dont overwrite rest of the records

Hi experts in the below query, I am actually overwriting all the records if any one customer number exists, please help corrct....

CREATE PROCEDURE [dbo].[CustomerMagSubscriptionSetFlag]

@p01_CustomerNo      INT      ,
@p02_OptInSel        VARCHAR(5),
@p03_BirthMonthSel      VARCHAR(9)

AS

SET NOCOUNT ON

BEGIN
-- Since all r new customers, insert the customer name, whether opted for a magazine or not --and the birth month selected on the website(this
-- serves more like personally identifying question and not necessarily the correct birth month , if the customer already exists overwrite with the entered optin and birthmonth

IF @p01_CustomerNo IN (SELECT Customer_No FROM G..CustMagSel)
BEGIN
UPDATE G..CustMagSel
SET Customer_No = @p01_CustomerNo,
OptInSel =
            CASE
      WHEN @p02_OptInSel      like '%Y%' THEN 'YES'
                  ELSE 'NO'
            END,
BirthMonthSel= @p03_BirthMonthSel
END

ELSE

INSERT INTO G..CustMagSel

SELECT Customer_No = @p01_CustomerNo,
OptInSel =
            CASE
                  WHEN @p02_OptInSel      like '%Y' THEN 'YES'
                  ELSE 'NO'
            END,
BirthMonthSel= @p03_BirthMonthSel

END

Jim Horn

In your UPDATE statement, since there is no WHERE clause, it updates all rows in G..CustMagSel.

Guessing you want to limit the rows that are UPDATED by including a WHERE clause?

sqlcurious

ASKER

yes but some how getting syntax error

Jim Horn

You'll have to copy-paste the T-SQL that is throwing the error into the question.

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION