Page 1 of 1

SQL Update Query

Posted: Fri Jun 22, 2007 2:44 pm
by ColleenH1983
Hello, I'm hoping someone can help me with the code for a SQL update query. I want to update null records to equal the previous record within the same column. Does SQL have an "UPDATE to previous" type function? Or do I need to write something in visual basic? If so, does anyone have the code? Thanks much...

Posted: Fri Jun 22, 2007 3:29 pm
by BDKR
Explain what you mean by "previous record". Is it a neighboring row in the table?

Posted: Fri Jun 22, 2007 3:31 pm
by ColleenH1983
Hi, sorry about that. By previous, I mean the record within the same column that corresponds to the current numeric primary key minus 1. Does that help? Thanks!

Posted: Fri Jun 22, 2007 5:22 pm
by RobertGonzalez
Moved :arrow: Databases.

Posted: Fri Jun 22, 2007 5:34 pm
by ReverendDexter
When do you need this to occur? Is this a periodic adjustment, or would it suit your purposes to have this done with a database trigger, so that those fields get their data upon insert?

What I mean is, are these rows being inserted with null values, or are they inserted with full values and losing their values somwhere along the line?

Posted: Fri Jun 22, 2007 9:24 pm
by bdlang
ColleenH1983 wrote: By previous, I mean the record within the same column that corresponds to the current numeric primary key minus 1.
Due to the way any given RDBMS sorts and stores records, and the way your application logic handles said records, it's not recommended to rely on 'the current numeric primary key minus 1'. Alot of people tend to try and use MAX(id) and all sort of hocus pocus to manage PK values.

Now, having said that, what you're describing doesn't make alot of sense to me; 'the record with the same column' - can you expand on that? Every record has a set of columns based on a PK value.

What database are you working with?

Posted: Sun Jun 24, 2007 1:12 pm
by ColleenH1983
Hi, thanks so much for your help. Let me try to clarify what I'm trying to do.

I'm importing data from a text file into an Access database. In the text file, there are headers that relate to the underlying data, and I need to paste these headers into the records that are currently null.

Ex:
Current Data

PK Employee Education Address
1 Sarah Jones UC Berkeley 10 Shattuck Ave.
2 UC Davis 6 Wisteria Lane
3 UCLA 701 Wilshire Blvd.
4 Mike Wong Stanford 576 Palo Alto Dr.
5 81 Cherry Ave.

Desired Data
PK Employee Education Address
1 Sarah Jones UC Berkeley 10 Shattuck Ave.
2 Sarah Jones UC Davis 6 Wisteria Lane
3 Sarah Jones UCLA 701 Wilshire Blvd.
4 Mike Wong Stanford 576 Palo Alto Dr.
5 Mike Wong Stanford 81 Cherry Dr.

I'm hoping for a simple query that will accomplish this. If I can't do it in SQL, is there another way? Do I need to write a visual basic macro?
Thank you again.

Posted: Mon Jun 25, 2007 10:28 am
by RobertGonzalez
Will all of the null 'Names' always follow the after the row that contains the users name? Is name the only field that will be empty?

Posted: Mon Jun 25, 2007 10:55 am
by jnb
ColleenH1983 wrote:Hi, thanks so much for your help. Let me try to clarify what I'm trying to do.

I'm importing data from a text file into an Access database. In the text file, there are headers that relate to the underlying data, and I need to paste these headers into the records that are currently null.
Couldn't you just record the previous rows values and as you insert each record replace null values with those previous values?

Is this running from a piece of PHP code handling each row in turn or are you trying to import the whole file with SQL only?

JB

Posted: Mon Jun 25, 2007 11:13 am
by ColleenH1983
Hi, there are almost 300,000 rows, and I'm not sure how to insert the previous value for each row without doing it individually.

I found an easy way to solve my problem though. I made a copy of the original table, added in new columns of the PK+1, PK+2, etc. then ran update queries aligning the original PK with the PK+1, PK+2, etc. and updated the null values. Thank you again for all your help!