Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update Query

Author

Topic

esti
Starting Member

12 Posts

Posted - 2006-04-27 : 12:14:43

Here is my problem:
I have a table with over 9 million records. There is a column called Department. There is another column called FeeCode. There are duplicate FeeCodes - it is not unique. Within the same FeeCode, some Departments are filled in, and some are blank.The ones that are filled in have the same department for each feeCode. I would like to update the blank Departments to match the ones that are filled in.
PLease Help!!
TIA
Esti

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-04-27 : 12:45:12

Esti,

Post your DDL (table schema) and some sample data (both
with and without the department filled in).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-27 : 12:48:57

If this doesn't work, then do what KenW suggests. If it looks like it would work, you may want to consider updating your 9 mil rows in smaller "chunks". You can add a where criteria for specific FeeCodes or ranges of FeeCodes. Then work through all the different FeeCodes in seperate batches.


update a set
	a.department = b.department
from	myTable a
join	(--Get feecode,department combinations where only 1 distinct departement value exists for the feecode
	select feecode
		,min(department) department
	from	myTable
	where	department is not null
	group by feecode
	having count(distinct department) = 1
	) b
		on b.feecode = a.feecode
where	a.department is null

Be One with the Optimizer
TG

esti
Starting Member

12 Posts

Posted - 2006-04-27 : 12:54:51

I'm not sure how to post the table schema or sample data. Here is the query I started running, in SQL Analyzer. I stopped it after 3 and 1/2 hours!
UPDATE Combo
SET Combo.FeeCode = Combo_1.FeeCode
FROM combo_1
WHERE ( (Combo.Dept Is Null) AND
(Combo_1.Dept Is Not Null) AND (Combo.Dept = combo_1.dept) )

Thanks for all your help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-27 : 13:04:12

>>I'm not sure how to post the table schema or sample data
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

>>I would like to update the blank Departments to match the ones that are filled in.
>>UPDATE Combo SET Combo.FeeCode = Combo_1.FeeCode
Are you updating Dept or FeeCode?

Follow the instructions in the link...

Be One with the Optimizer
TG

esti
Starting Member

12 Posts

Posted - 2006-04-27 : 13:16:03

Ooops I'm sorry, my mistake. Here is correct version. I will follow the link next.

UPDATE Combo
SET Combo.Dept = combo_1.dept
FROM combo_1
WHERE ( (Combo.Dept Is Null) AND
(Combo_1.Dept Is Not Null) AND (Combo.feeCode = Combo_1.feeCode) )

esti
Starting Member

12 Posts

Posted - 2006-04-27 : 13:36:05

Since there are 37 columns in the Combo table it would be hard to post the ddl. Can you use the above query to understand this?

esti
Starting Member

12 Posts

Posted - 2006-04-27 : 13:42:36

Here is the ddl. Where it says DrSussmanDepartment that is Dept. I shortened it for the above code.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Combo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Combo]
GO

CREATE TABLE [dbo].[Combo] (
[DrSussmanDept] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PanelDept] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Workstation] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MRN] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VisitNumber] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientClass] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientType] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Collect] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CollectionCenter] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DoctorCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DoctorName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateCollected] [datetime] NULL ,
[FromXtend] [int] NULL ,
[ScheduleCode] [int] NULL ,
[Price] [float] NULL ,
[BillDesc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BillingLogic] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int] NULL ,
[ABN] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ChargeType] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ICD9Code] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[APCaseNum] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PathologistCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LabCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateEntered] [datetime] NULL ,
[RequestNum] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ItemCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[Priority] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinancialClass] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LCDCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldMonth] [int] NULL ,
[fldYear] [int] NULL ,
[Pkey] [int] NOT NULL
) ON [PRIMARY]
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-27 : 14:17:50

FeeCode is not one of the columns listed, and combo_1 table is missing entirely. I have no clue how to help you...

Be One with the Optimizer
TG

esti
Starting Member

12 Posts

Posted - 2006-04-27 : 14:20:54

I'm sorry. Fee Code is actually ScheduleCode. Combo_1 is an exact replica of Combo. I'll understand if you want to give up on me!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-27 : 14:36:01

>>I'll understand if you want to give up on me!
Not quite yet

>>Combo_1 is an exact replica of Combo
Structure AND data? Do you need to use both tables to perform the update? Do both tables need to be updated? Why 2 identical tables? Is [Pkey] an actual Primary key?

btw, did you look at the code I proposed in my first post?
And follow what I meant by "chunking" the updates?

Be One with the Optimizer
TG

esti
Starting Member

12 Posts

Posted - 2006-04-27 : 15:19:24

Yes, structure and data. Pkey is an actual Primary Key. I am only updating one table, Combo. This is probably very convoluted, but I'm using Combo_1 to update Combo. Match on Schedule Code and copy the department from the one thats not null. Does this make sense? I tried to chunk out the data by month, but it didnt seem to save much time, so I stopped it.

esti
Starting Member

12 Posts

Posted - 2006-04-27 : 15:31:12

Ok. I can do it using 2 separate tables, instead of a copy of Combo. Here is how I write the query in Access and I would like to run it in Sql Server. Any idea how to translate this into sql server language?

UPDATE Combo INNER JOIN DeptWorkNew ON Combo.ScheduleCode = DeptWorkNew.schedule SET Combo.DrSussmanDept = [deptworknew]![drsussmandept]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-27 : 15:34:50

UPDATE c
SET DrSussmanDept = d.drsussmandept
FROM Combo c
INNER JOIN DeptWorkNew d
ON c.ScheduleCode = d.schedule

Tara Kizer
aka tduggan

esti
Starting Member

12 Posts

Posted - 2006-04-27 : 15:47:44

Trying this right now....

esti
Starting Member

12 Posts

Posted - 2006-04-27 : 16:08:40

And running for 22 minutes so far....