SQL Update Query
Moderator: General Moderators
-
ColleenH1983
- Forum Newbie
- Posts: 4
- Joined: Fri Jun 22, 2007 2:34 pm
SQL Update Query
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...
-
ColleenH1983
- Forum Newbie
- Posts: 4
- Joined: Fri Jun 22, 2007 2:34 pm
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
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?
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?
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.ColleenH1983 wrote: By previous, I mean the record within the same column that corresponds to the current numeric primary key minus 1.
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
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.
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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Couldn't you just record the previous rows values and as you insert each record replace null values with those previous values?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.
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
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!
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!