Using EXISTS

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jennifer1970@hotmail.com
    #1

    Using EXISTS

    I need to insert records into the table parSalesDetailM odifier from
    OLDparSalesDeta ilModifier where (1) those records DO NOT exit in
    parSalesDetailM odifier and (2) those records have a parent record in
    parSalesDetail.

    When I run the below query I get the error message that I am violating
    the Primary Key Constraint for parSalesDetailM odifier. In other words,
    it's trying to insert a record that does exist.

    Also posted below are create and insert startements for thte tables.

    If someone would be kind enough to show me what I am doing wrong, I'd
    really appreciate it.

    Thanks,
    Jennifer

    -------------------------------
    - STORED PROCEDURE
    -------------------------------
    CREATE Proc LoadModifier2

    @S datetime,
    @E datetime

    AS

    INSERT INTO ParSalesDetailM odifier
    (
    parSalesDetailM odifierID,
    parSalesHdrID,
    parSalesDetailI D,
    ModifierType,
    POSModifier,
    Condiment,
    CondimentPrice,
    UnitNumber,
    BusinessDay
    )

    SELECT
    OLD.parSalesDet ailModifierID,
    OLD.parSalesHdr ID,
    OLD.parSalesDet ailID,
    OLD.ModifierTyp e,
    OLD.POSModifier ,
    OLD.Condiment,
    OLD.CondimentPr ice,
    OLD.UnitNumber,
    OLD.BusinessDay

    FROM OldParSalesDeta ilModifier OLD

    WHERE

    EXISTS
    ( SELECT DET.parSalesHdr ID,
    DET.parSalesDet ailID,
    DET.UnitNumber,
    DET.BusinessDay
    FROM ParSalesDetail DET
    WHERE OLD.parSalesHdr ID = DET.parSalesHdr ID AND
    OLD.parSalesDet ailID = DET.parSalesDet ailID AND
    OLD.UnitNumber = DET.UnitNumber AND
    OLD.BusinessDay = DET.BusinessDay
    )

    AND

    NOT EXISTS
    ( SELECT NEW.parSalesHdr ID,
    NEW.parSalesDet ailID,
    NEW.parSalesDet ailModifierID,
    NEW.UnitNumber,
    NEW.BusinessDay
    FROM ParSalesDetailM odifier NEW
    WHERE OLD.parSalesHdr ID = NEW.parSalesHdr ID AND
    OLD.parSalesDet ailID = NEW.parSalesDet ailID AND
    OLD.parSalesDet ailModifierID = NEW.parSalesDet ailModifierID AND
    OLD.UnitNumber = NEW.UnitNumber AND
    OLD.BusinessDay = NEW.BusinessDay
    )

    AND OLD.BusinessDay between @S and @E

    -------------------------------
    - END STORED PROCEDURE
    -------------------------------


    -------------------------------
    - CREATE TABLES
    -------------------------------

    CREATE TABLE [parSalesDetailM odifier] (
    [ParSalesDetailM odifierID] [int] NOT NULL ,
    [parSalesHdrID] [int] NOT NULL ,
    [parSalesDetailI D] [int] NOT NULL ,
    [ModifierTYPE] [int] NULL ,
    [POSModifier] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
    ,
    [Condiment] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
    [CondimentPrice] [money] NOT NULL ,
    [UnitNumber] [int] NOT NULL ,
    [BusinessDay] [datetime] NOT NULL ,
    CONSTRAINT [PK_parSalesDeta ilModifier] PRIMARY KEY CLUSTERED
    (
    [BusinessDay],
    [UnitNumber],
    [parSalesHdrID],
    [parSalesDetailI D],
    [ParSalesDetailM odifierID]
    ) WITH FILLFACTOR = 70 ON [PRIMARY] ,
    CONSTRAINT [FK_parSalesDeta ilModifier_parS alesDetail] FOREIGN KEY
    (
    [BusinessDay],
    [UnitNumber],
    [parSalesHdrID],
    [parSalesDetailI D]
    ) REFERENCES [parSalesDetail] (
    [BusinessDay],
    [UnitNumber],
    [parSalesHdrID],
    [parSalesDetailI D]
    )
    ) ON [PRIMARY]
    GO

    CREATE TABLE [OLDparSalesDeta ilModifier] (
    [ParSalesDetailM odifierID] [int] NOT NULL ,
    [parSalesHdrID] [int] NOT NULL ,
    [parSalesDetailI D] [int] NOT NULL ,
    [ModifierTYPE] [int] NULL ,
    [POSModifier] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
    ,
    [Condiment] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
    [CondimentPrice] [money] NOT NULL ,
    [UnitNumber] [int] NOT NULL ,
    [BusinessDay] [datetime] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [parSalesDetail] (
    [parSalesHdrID] [int] NOT NULL ,
    [parSalesDetailI D] [int] NOT NULL ,
    [Before] [int] NOT NULL ,
    [Quantity] [int] NOT NULL ,
    [After] [int] NOT NULL ,
    [Promo] [money] NOT NULL ,
    [PromoBefore] [money] NOT NULL ,
    [ItemPrice] [money] NOT NULL ,
    [PromoAfter] [money] NOT NULL ,
    [POSItem] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
    [UnitNumber] [int] NOT NULL ,
    [Depleted] [char] (1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
    [AmountTenderTim e] [datetime] NULL ,
    [BusinessDay] [datetime] NOT NULL ,
    CONSTRAINT [PK_parSalesDeta il] PRIMARY KEY CLUSTERED
    (
    [BusinessDay],
    [UnitNumber],
    [parSalesHdrID],
    [parSalesDetailI D]
    ) WITH FILLFACTOR = 70 ON [PRIMARY] ,
    CONSTRAINT [FK_parSalesDeta il_parSalesHdr] FOREIGN KEY
    (
    [BusinessDay],
    [UnitNumber],
    [parSalesHdrID]
    ) REFERENCES [parSalesHdr] (
    [BusinessDay],
    [UnitNumber],
    [parSalesHdrID]
    )
    ) ON [PRIMARY]
    GO

    -------------------------------
    - END CREATE TABLES
    -------------------------------

    -------------------------------
    - INSERT INTO TABLES
    -------------------------------

    insert into parSalesDetailm odifier values (1,2298561,1091 7332,2,'ADD
    G-ON','ADD G-ON',.0000,2,'20 03-12-01')

    insert into oldparSalesDeta ilmodifier values (1,2298561,1091 7332,2,'ADD
    G-ON','ADD G-ON',.0000,2,'20 03-12-01')
    insert into oldparSalesDeta ilmodifier values (2,2298561,1091 7332,2,'SUB
    MAYO','SUB MAYO',.0000,2,' 2003-12-01')
    insert into oldparSalesDeta ilmodifier values
    (3,2298561,1091 7332,2,'TBBS',' TBBS',.0000,2,' 2003-12-01')
    insert into oldparSalesDeta ilmodifier values (1,2298561,1091 7340,2,'SUB
    MAYO','SUB MAYO',.0000,2,' 2003-12-01')
    insert into oldparSalesDeta ilmodifier values (2,2298561,1091 7340,2,'NO
    ONIN','NO ONIN',.0000,2,' 2003-12-01')
    insert into oldparSalesDeta ilmodifier values
    (3,2298561,1091 7340,2,'TBBS',' TBBS',.0000,2,' 2003-12-01')
    insert into oldparSalesDeta ilmodifier values
    (4,2298561,1091 7340,2,'WELL',' WELL',.0000,2,' 2003-12-01')
    insert into oldparSalesDeta ilmodifier values (1,2298561,1091 7341,2,'ADD
    G-ON','ADD G-ON',.0000,2,'20 03-12-01')
    insert into oldparSalesDeta ilmodifier values (2,2298561,1091 7341,2,'SUB
    MAYO','SUB MAYO',.0000,2,' 2003-12-01')
    insert into oldparSalesDeta ilmodifier values
    (3,2298561,1091 7341,2,'TBBS',' TBBS',.0000,2,' 2003-12-01')

    insert into parSalesDetail
    values(2298561, 10917332,0,1,0, .0000,.0000,3.4 900,.0000,'DM', 2,'N','2003-12-01
    10:00:02.000',' 2003-12-01')
    insert into parSalesDetail
    values(2298561, 10917340,0,1,0, .0000,.0000,.25 00,.0000,'JALA' ,2,'N','2003-12-01
    10:00:02.000',' 2003-12-01')
    insert into parSalesDetail
    values(2298561, 10917341,0,1, 0,.0000,.0000,1 .3400,.0000,'MD-DP',2,'N','2003-12-01
    10:00:02.000',' 2003-12-01')
    insert into parSalesDetail
    values(2298561, 10928910,0,1, 0,.0000,.0000,. 9900,.0000,'2PI E99',2,'N','200 3-12-01
    10:00:02.000',' 2003-12-01')
    insert into parSalesDetail
    values(2298561, 10928911,0,1, 0,.0000,.0000,. 5900,.0000,'DEC AF',2,'N','2003-12-01
    10:09:44.000',' 2003-12-01')
    insert into parSalesDetail
    values(2298561, 10928912,0,1, 0,.0000,.0000,1 .6900,.0000,'BO B-BAC',2,'N','200 3-12-01
    10:09:44.000',' 2003-12-01')
    insert into parSalesDetail
    values(2298561, 10929376,0,1, 0,.0000,.0000,. 5900,.0000,'COF FEE',2,'N','200 3-12-01
    10:00:44.000',' 2003-12-01')


    -------------------------------
    - END INSERT INTO TABLES
    -------------------------------

    Tags: None
    • Erland Sommarskog
      #2
      Re: Using EXISTS

      (jennifer1970@h otmail.com) writes:[color=blue]
      > I need to insert records into the table parSalesDetailM odifier from
      > OLDparSalesDeta ilModifier where (1) those records DO NOT exit in
      > parSalesDetailM odifier and (2) those records have a parent record in
      > parSalesDetail.
      >
      > When I run the below query I get the error message that I am violating
      > the Primary Key Constraint for parSalesDetailM odifier. In other words,
      > it's trying to insert a record that does exist.[/color]

      Not necessarily. It may be that among the rows that does not exist in
      the target table, there are duplicaates with regards to the primary key.
      I notice that the source table does not have any primary-key constraint,
      so duplicates cannot be excluded.

      You could comment away the INSERT part, so that all you have is the
      SELECT statement to inspect the output.

      I ran your repro, but I did not get any index violation.


      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server SP3 at
      Microsoft SQL Server
      http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
      Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

        Comment

        • jennifer1970@hotmail.com
          #3
          Re: Using EXISTS

          Erland,

          Thanks for your assistance. You are always such a help! I did comment
          out the insert bit and did just a select. On the real tables I got
          403300 records for 12/1/03. Doing just a select on the old Modifier
          table, without trying to leave out records that are not in the Detail
          table I got 403345 records. Checking the Modifiers production table,
          there are zero records for 12/1/03. I also got the same results as
          you when running my stored procedure on the test tables. I would not be
          surprised at all if there were duplicates in the source table. But
          wouldn't the NOT EXISTS part of the WHERE clause take care of that?

          Thanks!
          Jennifer

            Comment

            • Erland Sommarskog
              #4
              Re: Using EXISTS

              (jennifer1970@h otmail.com) writes:[color=blue]
              > Thanks for your assistance. You are always such a help! I did comment
              > out the insert bit and did just a select. On the real tables I got
              > 403300 records for 12/1/03. Doing just a select on the old Modifier
              > table, without trying to leave out records that are not in the Detail
              > table I got 403345 records. Checking the Modifiers production table,
              > there are zero records for 12/1/03. I also got the same results as
              > you when running my stored procedure on the test tables. I would not be
              > surprised at all if there were duplicates in the source table. But
              > wouldn't the NOT EXISTS part of the WHERE clause take care of that?[/color]

              Yes, for the data that is already in the table prior to the insert. But
              the NOT EXISTS is not re-evaluated once a row has been inserted. This
              example demonstrates what I mean:

              CREATE TABLE target (a int NOT NULL PRIMARY KEY)
              go
              CREATE TABLE source (a int NOT NULL)
              go
              INSERT source VALUES (98)
              INSERT source VALUES (98)
              go
              INSERT target (a)
              SELECT a FROM source s
              WHERE NOT EXISTS (SELECT * FROM target t WHERE s.a = t.a)
              go
              DROP TABLE target, source

              Just as a side note: more than I have been about going crazy when I have
              run into a PK violation with an INSERT SELECT WHERE NOT EXISTS, because
              I just have not been able to understand how the NOT EXISTS could fail.
              Not until after some time, I have realized that the duplicates were in
              the new rows, and I that I need a DISTINCT, better join conditions or
              whatever.

              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server SP3 at
              Microsoft SQL Server
              http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
              Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

                Comment

                • jennifer1970@hotmail.com
                  #5
                  Re: Using EXISTS

                  Erland,

                  You were absolutely right - there were duplicates, which brings me to
                  another question. The thing is, the duplicates are not *really*
                  duplicates. There is a field called parSalesDetailM odifierID, which
                  should be unique for each modifier to the detail record.

                  For example, the table could have 2 records that modfiy one detail
                  record. One would have a ParSalesDetailM odifierID of 1 and the other
                  would be 2. But there are some cases where the
                  ParSalesDetailM odifierID is 1 for both cases. But the condiment may be
                  different - one may be to add cheese and the other may be add a patty.
                  We really do need to keep both records.

                  So now it is my job to find these offending records and renumber the
                  ParSalesDetailM odifierID.

                  I thought I had it worked out. And I am almost positive that once I
                  write it all down here I'll see where I am going wrong. :) I thought
                  I'd use a cursor and loop through the repeaters and renumber them.
                  BUT.... I never seem to get out of the second cursor loop. It hangs
                  there and keeps renumbering the first record over and over again. It
                  looks like @@FETCH_STATUS is always zero.

                  Any ideas?

                  Thanks,
                  Jennifer

                  Declare @BusDay datetime
                  Declare @Unit int
                  Declare @HdrID int
                  Declare @DetID int

                  Declare @BusDay2 datetime
                  Declare @Unit2 int
                  Declare @HdrID2 int
                  Declare @DetID2 int
                  Declare @ModID2 int
                  Declare @POSModifier varchar(20)
                  Declare @Condiment varchar(20)
                  Declare @X int

                  -- CREATE THE FIRST CURSOR

                  DECLARE Mods CURSOR FOR
                  select distinct businessday, unitnumber, parsaleshdrid,
                  parsalesdetaili d
                  from oldParSalesDeta ilModifier
                  where BusinessDay = '12/1/03' and unitnumber = 2 --and ParSalesHdrID =
                  2298561 and ParSalesDetailI D = 10917332
                  group by businessday, unitnumber, parsaleshdrid, parsalesdetaili d
                  HAVING Count(*) > 1
                  order by businessday, unitnumber, parsaleshdrid, parsalesdetaili d

                  --OPEN THE FIRST CURSOR
                  OPEN Mods

                  --FETCH FIRST FROM FIRST CURSOR
                  FETCH NEXT FROM Mods into @BusDay, @Unit, @HdrID, @DetID

                  --BEGIN LOOP FOR FIRST CURSOR
                  WHILE @@FETCH_STATUS = 0
                  BEGIN
                  -- CREATE SECOND CURSOR
                  DECLARE Mods2 CURSOR FOR
                  SELECT BusinessDay, UnitNumber, parSalesHdrID, parSalesDetailI D,
                  parSalesDetailM odifierID, POSModifier, Condiment
                  FROM oldParSalesDeta ilModifier
                  WHERE BusinessDay = @BusDay and UnitNumber = @Unit and
                  ParSalesHdrID = @HdrID and ParSalesDetailI D = @DetID

                  -- MAKE SURE X STARTS OUT AS 1
                  Set @X = 1

                  -- OPEN THE SECOND CURSOR
                  OPEN Mods2

                  -- FETCH FIRST FROM THE SECOND CURSOR
                  FETCH NEXT FROM Mods2 into @BusDay2, @Unit2, @HdrID2, @DetID2,
                  @ModID2, @POSModifier, @Condiment

                  --BEGIN LOOP FOR SECOND CURSOR
                  WHILE @@FETCH_STATUS = 0
                  BEGIN
                  -- UPDATE MODIFIER TABLE
                  UPDATE oldParSalesDeta ilModifier
                  SET parSalesDetailM odifierID = @X

                  --SELECT @X, * from oldParSalesDeta ilModifier
                  WHERE BusinessDay = @BusDay and UnitNUmber = @Unit and
                  ParSalesHdrID = @HdrID and ParSalesDetailI D = @DetID and
                  parSalesDetailM odifierID = @ModID2 and POSModifier = @POSModifier
                  and
                  Condiment = @Condiment


                  print 'X: ' + Cast(@X as nvarchar(20)) + ' Unit: ' + Cast(@Unit as
                  nvarchar(20)) + ' HdrID: ' + Cast(@HdrID as nvarchar(20)) + ' DetID: '
                  + Cast(@DetID as nvarchar(20)) + ' ModID: ' + Cast(@ModID2 as
                  nvarchar(20)) + ' Modifier: ' + @POSModifier + ' Condiment ' +
                  @Condiment

                  -- INCREMENT X
                  Set @X = @X + 1

                  -- FETCH NEXT FROM SECOND CURSOR
                  FETCH NEXT FROM Mods2 into @BusDay2, @Unit2, @HdrID2, @DetID2,
                  @ModID2, @POSModifier, @Condiment
                  END

                  -- CLOSE AND DEALLAOCATE SECOND CURSOR
                  CLOSE Mods2
                  DEALLOCATE Mods2

                  -- FETCH NEXT RECORD FROM FIRST CURSOR
                  FETCH NEXT FROM Mods into @BusDay, @Unit, @HdrID, @DetID
                  END

                  -- CLOSE AND DEALLOCATE THE FIRST CURSOR
                  CLOSE Mods
                  DEALLOCATE Mods

                    Comment

                    • Erland Sommarskog
                      #6
                      Re: Using EXISTS

                      (jennifer1970@h otmail.com) writes:[color=blue]
                      > I thought I had it worked out. And I am almost positive that once I
                      > write it all down here I'll see where I am going wrong. :) I thought
                      > I'd use a cursor and loop through the repeaters and renumber them.
                      > BUT.... I never seem to get out of the second cursor loop. It hangs
                      > there and keeps renumbering the first record over and over again. It
                      > looks like @@FETCH_STATUS is always zero.
                      >
                      > Any ideas?[/color]

                      Try adding INSENSITIVE before CURSOR. That finalizes the result set for
                      the cursor once for all. The default keyset-driven cursors may pick
                      up your updates and feed you back the row you just changed.



                      --
                      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                      Books Online for SQL Server SP3 at
                      Microsoft SQL Server
                      http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
                      Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

                        Comment

                        • jennifer1970@hotmail.com
                          #7
                          Re: Using EXISTS

                          Erland,

                          You are totally awesome! That did the trick!

                          Thanks!!!
                          Jennifer

                            Comment

                            • Erland Sommarskog
                              #8
                              Re: Using EXISTS

                              (jennifer1970@h otmail.com) writes:[color=blue]
                              > You are totally awesome! That did the trick![/color]

                              Glad to hear that it worked out!

                              I've gone to the point that I always use INSENSITIVE for cursors. I have
                              had so many problems with the default keyset-driven cursors. Not so much
                              funky results, as truly absymal query plans.


                              --
                              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                              Books Online for SQL Server SP3 at
                              Microsoft SQL Server
                              http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
                              Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

                                Comment

                                Previous template Next
                                Working...