SQL Update Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ColleenH1983
Forum Newbie
Posts: 4
Joined: Fri Jun 22, 2007 2:34 pm

SQL Update Query

Post 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...
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

Explain what you mean by "previous record". Is it a neighboring row in the table?
ColleenH1983
Forum Newbie
Posts: 4
Joined: Fri Jun 22, 2007 2:34 pm

Post 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!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Moved :arrow: Databases.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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?
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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?
ColleenH1983
Forum Newbie
Posts: 4
Joined: Fri Jun 22, 2007 2:34 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
jnb
Forum Newbie
Posts: 1
Joined: Mon Jun 25, 2007 10:38 am

Post 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
ColleenH1983
Forum Newbie
Posts: 4
Joined: Fri Jun 22, 2007 2:34 pm

Post 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!
Post Reply