?
Solved

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

Posted on 2012-08-10
6
Medium Priority
?
499 Views
Last Modified: 2012-08-22
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
0
Comment
Question by:sqlcurious
  • 3
  • 2
6 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38281729
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?
0
 

Author Comment

by:sqlcurious
ID: 38281871
yes but some how getting syntax error
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38281881
You'll have to copy-paste the T-SQL that is throwing the error into the question.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 38282566
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
      WHERE Customer_No = Something --  You'll need to add something like this to limit the number of rows that gets updated
      END
ELSE
      -- ...
END
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 38288370
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
 WHERE                                                  --<<--
        Customer_No = @p01_CustomerNo  --<<--
 END
0
 

Author Closing Comment

by:sqlcurious
ID: 38321146
thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question