August 21, 2009 at 9:30 am
Hello
I wondered if anyone could help. This is probably very simple.
I have a table with three columns
FirstName, Surname, E-Mail Address
The rows are not discrete and the table contains duplicate rows, but I need it to be this way.
What I want to do is write an update query for the email field so that its value will be set as per similar previous rows.
For example the table contains the entries...
FirstName, Surname, E-Mail Address
A, ASurname, A EMail Address
B, BSurname, B EMail Address
C, CSurname, C EMail Address
then User A submits another record (but they do not insert their E Mail Address)...
FirstName, Surname, E-Mail Address
A, ASurname, A EMail Address
B, BSurname, B EMail Address
C, CSurname, C EMail Address
A, ASurname, NULL
What I want is for the update query to update the EMail Field based on the first record so that the table reads...
A, ASurname, A EMail Address
B, BSurname, B EMail Address
C, CSurname, C EMail Address
A, ASurname, A EMail Address
Can any one please help?
August 21, 2009 at 9:36 am
What do you want to do if someone is in there with two or more different e-mail addresses? Just make it so every entry has the first e-mail address?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2009 at 9:41 am
Well Yes, I don't think that there will be that many people with the same First Name and Surname. It will not be a very big database.
August 21, 2009 at 10:24 am
Is this what you are looking for:
UPDATE TableName
SET email=b.email
FROM TableName a
JOIN (SELECT * FROM TableName WHERE email IS NOT NULL) b
ON a.firstname=b.firstname AND a.surname=b.surname AND a.email IS NULL
HTH,
Sups
August 21, 2009 at 10:42 am
No I dont think so because there is only one table. I want to update the table from itself if you see what I mean. I think that your query is updating one table from another table?
August 21, 2009 at 10:49 am
No, I am using the same table. Its kind of a self-join. Try replacing "TableName" with your table's name and replacing column names with the right column names.
Let me know if its not working out for you.
-Sups
August 21, 2009 at 11:07 am
YES! It appears to be working. I don't understand the query and what the a. and b. are for, but it works. Thank you ever so much for your help. I am extremely grateful!
August 21, 2009 at 11:49 am
I am glad it worked out for you.
As for what the "a" and "b" are for google "table alias name" to get more detailed info.
August 22, 2009 at 10:16 am
Thanks I will look into that.
But then I was wondering, if somebody were to enter an incorrect e-mail address initially, this would insert the incorrect e-mail address into all other records with the same First Name and Surname. Is there any way to modify this update query so that if you were to notice an incorrect e-mail address in a record, and you then modified it to the correct e-mail address, then subsequently all the other e-mail address fields (with the same corresponding First Name and Surname) would be updated too? What I mean is, rather than asking the update query to look for e-mail fields which are null, and update them with contents e-mail fields which are not null and which have the same corresponding First Name and Surname, could you ask it to look at the e-mail field which was most recently updated, and then use the contents of this field to update all other fields with the same First Name and Surname?
Many Thanks once again for your help
August 22, 2009 at 11:04 am
conmcgarry (8/22/2009)
Thanks I will look into that.But then I was wondering, if somebody were to enter an incorrect e-mail address initially, this would insert the incorrect e-mail address into all other records with the same First Name and Surname. Is there any way to modify this update query so that if you were to notice an incorrect e-mail address in a record, and you then modified it to the correct e-mail address, then subsequently all the other e-mail address fields (with the same corresponding First Name and Surname) would be updated too? What I mean is, rather than asking the update query to look for e-mail fields which are null, and update them with contents e-mail fields which are not null and which have the same corresponding First Name and Surname, could you ask it to look at the e-mail field which was most recently updated, and then use the contents of this field to update all other fields with the same First Name and Surname?
Many Thanks once again for your help
If you were to notice (or were notified) that John Doe's email address was wrong, you'd simply do this:
update dbo.Tablename set
EMailAddr = 'correct-email-address'
where
FirstName = 'John' and
SurName = 'Doe';
This would update all of John Doe's records.
August 22, 2009 at 11:42 am
Yes, but unfortunately I cannot do this.
The server my database is on has a VPN Connection. I cannot access the database from work due to the fact that my work's firewall will not let me connect to the database so that I can run this query. The result is that I have to rely on the ASP pages which I have set up to submit data to the db to execute stored procedures to do things like this. I do not know how to make the ASP Pages ask for parameters for any query, and your query needs parameters I think. You need to tell the query what the old (incorrect) details are, and what the new correct ones are. What I was hoping for was a query that would
1. Look at the last record updated (with the correct details which I have just updated)
2. Look for all other records with the same First Name and Surname eg; John Doe
3. and then update all previous such records with the correct e-mail address from the last update record.
Is this doable do you think?
Many Thanks for your help.
August 22, 2009 at 11:42 am
Yes, but unfortunately I cannot do this.
The server my database is on has a VPN Connection. I cannot access the database from work due to the fact that my work's firewall will not let me connect to the database so that I can run this query. The result is that I have to rely on the ASP pages which I have set up to submit data to the db to execute stored procedures to do things like this. I do not know how to make the ASP Pages ask for parameters for any query, and your query needs parameters I think. You need to tell the query what the old (incorrect) details are, and what the new correct ones are. What I was hoping for was a query that would
1. Look at the last record updated (with the correct details which I have just updated)
2. Look for all other records with the same First Name and Surname eg; John Doe
3. and then update all previous such records with the correct e-mail address from the last update record.
Is this doable do you think?
Many Thanks for your help.
August 22, 2009 at 11:44 am
Yes, but unfortunately I cannot do this.
The server my database is on has a VPN Connection. I cannot access the database from work due to the fact that my work's firewall will not let me connect to the database so that I can run this query. The result is that I have to rely on the ASP pages which I have set up to submit data to the db to execute stored procedures to do things like this. I do not know how to make the ASP Pages ask for parameters for any query, and your query needs parameters I think. You need to tell the query what the old (incorrect) details are, and what the new correct ones are. What I was hoping for was a query that would
1. Look at the last record updated (with the correct details which I have just updated)
2. Look for all other records with the same First Name and Surname eg; John Doe
3. and then update all previous such records with the correct e-mail address from the last update record.
Is this doable do you think?
Many Thanks for your help.
August 24, 2009 at 11:13 am
Is there any way to modify this update query so that if you were to notice an incorrect e-mail address in a record, and you then modified it to the correct e-mail address, then subsequently all the other e-mail address fields (with the same corresponding First Name and Surname) would be updated too?
First of all how are you doing this? i.e. how are you updating the incorrect email address?
August 24, 2009 at 3:18 pm
I'm sorry, I'm not quite sure what you mean.
The incorrect e-mail address would be updated via an asp edit page. Let's just say the person was John Doe. You could search for all records where first name is John and surname is Doe. If however there were a lot of records these would all have the incorrect (or old) e-mail address and you would have to edit each one individually which could be time consuming if there were a lot of records. Lets's say that John Doe has submitted 100 records to the database and that his e-mail address was JD@hotmail.com when he submitted them. He then however lets me know that he has changed his e-mail address to JD@Yahoo.com. All records from John Doe say that his address is JD@hotmail.com. What I would like to do is just search for records where first name = John and surname = Doe. I will see 100 records, and the e-mail address in all of these will be the old e-mail address - JD@hotmail.com. I then change the e-mail address in just one of these to JD@Yahoo.com. What I would like the update query to do is to look at this most recently updated record, see that the e-mail address in this is JD@Yahoo.com, look for all other records where first name is John and surname is Doe, and then change all the e-mail addresses in these previous records from JD@hotmail.com to JD@Yahoo.com.
Is that possible or am I just being silly?
Many many thanks for your help.
Con.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply