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 SQL record to previous

Author  Topic 

ColleenH1983
Starting Member

7 Posts

Posted - 2007-06-22 : 16:30:07
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
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-22 : 16:33:17
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 - 2007-06-22 : 16:43:27
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 - 2007-06-22 : 16:44:45
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
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-22 : 16:50:17
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 - 2007-06-22 : 16:56:55
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
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-22 : 17:07:23
[code]
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
[/code]

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

ColleenH1983
Starting Member

7 Posts

Posted - 2007-06-22 : 17:18:15
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
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-22 : 17:20:39
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 - 2007-06-22 : 17:35:52
Thank you again. I'm heading out, but I'll try it Monday!
Go to Top of Page

johnsql
Posting Yak Master

161 Posts

Posted - 2007-11-12 : 12:52:05
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
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-12 : 13:04:29
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
   

- Advertisement -