SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Update SQL record to previous
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ColleenH1983
Starting Member

7 Posts

Posted - 06/22/2007 :  16:30:07  Show Profile  Reply with Quote
Hello, I'm trying to create an update query that will update my null records to be equal to the previous record within the same column. By previous, I mean equal to the current numeric primary key minus one. I had to import some data from a text file, and the text file has a lot of null values that make sense as headers when you look at the page visually, but that don't make sense in Access. It's roughly 264K rows, so too long for me to import to/from Excel. Thanks so much!

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 06/22/2007 :  16:33:17  Show Profile  Visit dinakar's Homepage  Reply with Quote
Can you provide some sample data and expected output?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

ColleenH1983
Starting Member

7 Posts

Posted - 06/22/2007 :  16:43:27  Show Profile  Reply with Quote
Thanks again. I'm hoping for something like the following:

Current Data

Column1 Column2 Column3 Column4
EmployeeA UC Berkeley 2001 10 Arlington RD.
UC Davis 2005 861 Trees Lane
UC Santa Cruz 2007 654 Frog St. Apt. 3
EmployeeB UCLA 2001 10 Wilshire Blvd.

Desired Data

Column1 Column2 Column3 Column4
EmployeeA UC Berkeley 2001 10 Arlington RD.
EmployeeA UC Davis 2005 861 Trees Lane
EmployeeA UC Santa Cruz 2007 654 Frog St. Apt. 3
EmployeeB UCLA 2001 10 Wilshire Blvd.


I would like the "EmployeeA" to be pasted down until hitting the text "EmployeeB." The data is all from a text file that was using the "Employee A" as a header for everything corresponding to it

Thank you so much. This will really help me out.
Go to Top of Page

ColleenH1983
Starting Member

7 Posts

Posted - 06/22/2007 :  16:44:45  Show Profile  Reply with Quote
Hi, you can't tell in the reply above because of the formatting, but in the Current Data, imagine the fields under "EmployeeA" are null until hitting "EmployeeB." Thanks!
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 06/22/2007 :  16:50:17  Show Profile  Visit dinakar's Homepage  Reply with Quote
Is there any primary key (perhaps numeric) in the table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

ColleenH1983
Starting Member

7 Posts

Posted - 06/22/2007 :  16:56:55  Show Profile  Reply with Quote
Apologies again. I added it below

Current Data

PrimaryKey Column2 Column3 Column4 Column5
1EmployeeA UC Berkeley 2001 10 Arlington RD.
2UC Davis 2005 861 Trees Lane
3UC Santa Cruz 2007 654 Frog St. Apt. 3
4EmployeeB UCLA 2001 10 Wilshire Blvd.

Desired Data

PrimaryKeyColumn2 Column3 Column4 Column5
1EmployeeA UC Berkeley 2001 10 Arlington RD.
2EmployeeA UC Davis 2005 861 Trees Lane
3EmployeeA UC Santa Cruz 2007 654 Frog St. Apt. 3
4EmployeeB UCLA 2001 10 Wilshire Blvd.
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 06/22/2007 :  17:07:23  Show Profile  Visit dinakar's Homepage  Reply with Quote

select 
	PrimaryKey, 
	column1 = (Case when Column1 is null then (Select column1 from YourTable T2 Where T2.PrimaryKey < T.PrimaryKey And Column1 Is Not null) Else Column1 end) 
	,column2
	,column3
	,column4 
from YourTable T


Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

ColleenH1983
Starting Member

7 Posts

Posted - 06/22/2007 :  17:18:15  Show Profile  Reply with Quote
Thank you so much! I have one quick question, what is T2? Should I make another copy of the original table and use that?
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 06/22/2007 :  17:20:39  Show Profile  Visit dinakar's Homepage  Reply with Quote
No its the same table aliased with a different name. IF the column is null we want to do an instant SELECT from the same table again and find the first column1 that is not empty based on the PrimaryKey value. So you dont need to create a duplicate copy of the table.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

ColleenH1983
Starting Member

7 Posts

Posted - 06/22/2007 :  17:35:52  Show Profile  Reply with Quote
Thank you again. I'm heading out, but I'll try it Monday!
Go to Top of Page

johnsql
Posting Yak Master

USA
161 Posts

Posted - 11/12/2007 :  12:52:05  Show Profile  Reply with Quote
quote:
Originally posted by dinakar


select 
	PrimaryKey, 
	column1 = (Case when Column1 is null then (Select column1 from YourTable T2 Where T2.PrimaryKey < T.PrimaryKey And Column1 Is Not null) Else Column1 end) 
	,column2
	,column3
	,column4 
from YourTable T


Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/



What if we have data like

insert #t
select 1 , 'EmployeeA', 'UC Berkeley', 2001, ' 10 Arlington RD.'
union all select 2, null, 'UC Davis', 2005, ' 861 Trees Lane'
union all select 3, null, 'UC Santa Cruz', 2007 , '654 Frog St. Apt. 3'
union all select 4, null, 'UCSC', 2001 , '12399 SunSet Blvd. Apt. 3'
union all select 5, 'EmployeeB', 'UCLA', 2001, '10 Wilshire Blvd.'
union all select 6, null, 'UTW', 1999, '100 GreenView Rd.'
union all select 7, null, 'UAG', 1996, '786 OakThreees Dr.'
union all select 8, 'EmployeeC', 'UTH', 2002, '96 Sherman Dr.'
union all select 9, null, 'UWS', 2000, '1226 HWY 55 Suite # 102'
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 11/12/2007 :  13:04:29  Show Profile  Visit dinakar's Homepage  Reply with Quote
quote:
Originally posted by johnsql

quote:
Originally posted by dinakar


select 
	PrimaryKey, 
	column1 = (Case when Column1 is null then (Select column1 from YourTable T2 Where T2.PrimaryKey < T.PrimaryKey And Column1 Is Not null) Else Column1 end) 
	,column2
	,column3
	,column4 
from YourTable T


Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/



What if we have data like

insert #t
select 1 , 'EmployeeA', 'UC Berkeley', 2001, ' 10 Arlington RD.'
union all select 2, null, 'UC Davis', 2005, ' 861 Trees Lane'
union all select 3, null, 'UC Santa Cruz', 2007 , '654 Frog St. Apt. 3'
union all select 4, null, 'UCSC', 2001 , '12399 SunSet Blvd. Apt. 3'
union all select 5, 'EmployeeB', 'UCLA', 2001, '10 Wilshire Blvd.'
union all select 6, null, 'UTW', 1999, '100 GreenView Rd.'
union all select 7, null, 'UAG', 1996, '786 OakThreees Dr.'
union all select 8, 'EmployeeC', 'UTH', 2002, '96 Sherman Dr.'
union all select 9, null, 'UWS', 2000, '1226 HWY 55 Suite # 102'



I have no idea what your question is. Please open a new thread with your question, sample data and expected output.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000