Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

update Expand / Collapse
Author
Message
Posted Tuesday, July 21, 2009 8:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 4, 2014 3:57 PM
Points: 159, Visits: 317

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

Post #756643
Posted Tuesday, July 21, 2009 8:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:47 AM
Points: 273, Visits: 441
How many records are in each table? Are there any duplicates?
Post #756655
Posted Tuesday, July 21, 2009 8:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 4, 2014 3:57 PM
Points: 159, Visits: 317
TempDelete has 43k records.

distinct genleveltolevelrelid records

Post #756661
Posted Tuesday, July 21, 2009 8:53 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:47 AM
Points: 273, Visits: 441
What about tblLevelToLevelRel?
Post #756664
Posted Tuesday, July 21, 2009 9:07 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 4, 2014 3:57 PM
Points: 159, Visits: 317
the genleveltolevelrel id is a primary key on the tblleveltolevelrel table.
Post #756675
Posted Tuesday, July 21, 2009 9:20 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:47 AM
Points: 273, Visits: 441
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


Post #756685
Posted Tuesday, July 21, 2009 10:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 4, 2014 3:57 PM
Points: 159, Visits: 317
first query 43k

second 153k
Post #756740
Posted Tuesday, July 21, 2009 10:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:47 AM
Points: 273, Visits: 441
Are each of these columns in the tblleveltolevelrel table?active,changeddate,changedmachinename
Or are they in the TempDelete table?
Post #756749
Posted Tuesday, July 21, 2009 10:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 4, 2014 3:57 PM
Points: 159, Visits: 317



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
Post #756755
Posted Tuesday, July 21, 2009 10:19 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 19, 2013 8:47 AM
Points: 273, Visits: 441
Hmm, so the problem is fixed then?
Post #756760
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse