| Author |
 Topic  |
|
|
ColleenH1983
Starting Member
7 Posts |
 Posted - 06/22/2007 : 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
Flowing Fount of Yak Knowledge
USA
2507 Posts |
Posted - 06/22/2007 : 16:33:17
|
Can you provide some sample data and expected output?
Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
ColleenH1983
Starting Member
7 Posts |
Posted - 06/22/2007 : 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.
|
|
|
|
ColleenH1983
Starting Member
7 Posts |
Posted - 06/22/2007 : 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! |
|
|
|
dinakar
Flowing Fount of Yak Knowledge
USA
2507 Posts |
Posted - 06/22/2007 : 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/ |
|
|
|
ColleenH1983
Starting Member
7 Posts |
Posted - 06/22/2007 : 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. |
|
|
|
dinakar
Flowing Fount of Yak Knowledge
USA
2507 Posts |
Posted - 06/22/2007 : 17:07:23
|
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/ |
|
|
|
ColleenH1983
Starting Member
7 Posts |
Posted - 06/22/2007 : 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? |
|
|
|
dinakar
Flowing Fount of Yak Knowledge
USA
2507 Posts |
Posted - 06/22/2007 : 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/ |
|
|
|
ColleenH1983
Starting Member
7 Posts |
Posted - 06/22/2007 : 17:35:52
|
| Thank you again. I'm heading out, but I'll try it Monday! |
|
|
|
johnsql
Posting Yak Master
USA
161 Posts |
Posted - 11/12/2007 : 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' |
|
|
|
dinakar
Flowing Fount of Yak Knowledge
USA
2507 Posts |
Posted - 11/12/2007 : 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/ |
|
|
| |
Topic |
|
|
|
Update SQL record to previous
