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.
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/ |
|
|
ColleenH1983
Starting Member
7 Posts |
Posted - 2007-06-22 : 16:43:27
|
Thanks again. I'm hoping for something like the following:Current DataColumn1 Column2 Column3 Column4EmployeeA UC Berkeley 2001 10 Arlington RD. UC Davis 2005 861 Trees Lane UC Santa Cruz 2007 654 Frog St. Apt. 3EmployeeB UCLA 2001 10 Wilshire Blvd.Desired DataColumn1 Column2 Column3 Column4EmployeeA UC Berkeley 2001 10 Arlington RD. EmployeeA UC Davis 2005 861 Trees LaneEmployeeA UC Santa Cruz 2007 654 Frog St. Apt. 3EmployeeB 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 itThank you so much. This will really help me out. |
|
|
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! |
|
|
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/ |
|
|
ColleenH1983
Starting Member
7 Posts |
Posted - 2007-06-22 : 16:56:55
|
Apologies again. I added it belowCurrent DataPrimaryKey Column2 Column3 Column4 Column51EmployeeA UC Berkeley 2001 10 Arlington RD. 2UC Davis 2005 861 Trees Lane3UC Santa Cruz 2007 654 Frog St. Apt. 34EmployeeB UCLA 2001 10 Wilshire Blvd.Desired DataPrimaryKeyColumn2 Column3 Column4 Column51EmployeeA UC Berkeley 2001 10 Arlington RD. 2EmployeeA UC Davis 2005 861 Trees Lane3EmployeeA UC Santa Cruz 2007 654 Frog St. Apt. 34EmployeeB UCLA 2001 10 Wilshire Blvd. |
|
|
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/ |
|
|
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? |
|
|
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/ |
|
|
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! |
|
|
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 likeinsert #tselect 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
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 likeinsert #tselect 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/ |
|
|
|
|
|
|
|