Post reply

update

  • July 21, 2009 at 8:34 am

    #133984

    I ran the following select, and then the following update.

    The select returns 43k records, the update returns 153k. Why isn't the update working as expected?

    No update triggers on this table are enabled.

    SELECT tblLevelToLevelRel.Active

    FROM tblLevelToLevelRel INNER JOIN

    TempDelete ON tblLevelToLevelRel.GenLevelToLevelRelID = TempDelete.genleveltolevelrelid

    begin tran

    update tblleveltolevelrel set active = 0, changeddate = getdate(), changedmachinename = @@servername

    FROM tblLevelToLevelRel INNER JOIN

    TempDelete ON tblLevelToLevelRel.GenLevelToLevelRelID = TempDelete.genleveltolevelrelid

  • July 21, 2009 at 8:45 am

    #1027674

    How many records are in each table? Are there any duplicates?

  • July 21, 2009 at 8:51 am

    #1027680

    TempDelete has 43k records.

    distinct genleveltolevelrelid records

  • July 21, 2009 at 8:53 am

    #1027683

    What about tblLevelToLevelRel?

  • July 21, 2009 at 9:07 am

    #1027692

    the genleveltolevelrel id is a primary key on the tblleveltolevelrel table.

  • July 21, 2009 at 9:20 am

    #1027699

    How many records do you get when you run each of the following statements?

    SELECT tblLevelToLevelRel.Active,changeddate,changedmachinename

    FROM tblLevelToLevelRel INNER JOIN

    TempDelete ON tblLevelToLevelRel.GenLevelToLevelRelID = TempDelete.genleveltolevelrelid

    Select * from tblLevelToLevelRel

  • July 21, 2009 at 10:02 am

    #1027746

    first query 43k

    second 153k

  • July 21, 2009 at 10:10 am

    #1027752

    Are each of these columns in the tblleveltolevelrel table?active,changeddate,changedmachinename

    Or are they in the TempDelete table?

  • July 21, 2009 at 10:15 am

    #1027756

    CREATE TABLE [dbo].[tblLevelToLevelRel](

    [GenLevelToLevelRelID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [UpperLevelCloneID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [LowerLevelCloneID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [AllocPercent] [float] NOT NULL,

    [Active] [bit] NULL,

    [ChangedDate] [datetime] NULL,

    [ChangedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ChangedType] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ChangedMachineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    the following query ran to update 153k records. I am having to do a database restore becaue of it.

    Now when I look to run the following query, it wants to update 43k records. I haven't a clue why.

    And I don't know if there is an answer to be honest.

    begin tran

    update tblleveltolevelrel set active = 0, changeddate = getdate(), changedmachinename = @@servername

    FROM tblLevelToLevelRel INNER JOIN

    TempDelete ON tblLevelToLevelRel.GenLevelToLevelRelID = TempDelete.genleveltolevelrelid

    commit

  • July 21, 2009 at 10:19 am

    #1027760

    Hmm, so the problem is fixed then?

  • July 21, 2009 at 10:36 am

    #1027786

    If the problem is my brain, it isn't fixed.

    Please fix my brain.

  • July 21, 2009 at 10:54 am

    #1027802

    I'm afraid I'm the mechanic with all the broken cars. Would you really want me to help? 😉

  • July 21, 2009 at 12:29 pm

    #1027844

    what are you using to determine what is "returned" from the update?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply