Using an update query where details are shared 2

[frankis]

I have a table of applicants which has a unique applicant number as the primary key, however the same applicants have been entered again and again. I am trying to clean this up step by step. Currently I am trying to update the email field if it has been entered in one of the applicant records. ie John Smith may have several applicant records but only one of them has an email, I need to update of the applicant records relating to him with the email address in one of the records.
I dont want to delete the duplicates at this stage as the details in some of the records may not e current.

Hope someone can shed some light on this one.

 

[Golom]

UPDATE myTable T INNER JOIN 
       (Select ApplicantName, MAX(email) As [GoodEmail]
        FROM myTable
        Group By ApplicantName
        HAVING MAX(email) IS NOT NULL) Q
       ON T.ApplicantName = Q.ApplicantName

SET T.email = Q.GoodEmail

 

[frankis]

Thanks Golom but its from the same table that I need to run the update eg.

Applicant ID Applicant_Name Applicant_Email

1 John john@x.com
2 Mark Mark@x.com
3 John
4 Mark
5 Fred fred@what.com

Basically I want to update all records for John with his email..

many thanks

 

[Golom]

That IS from the same table

UPDATE [COLOR=red]myTable[/color] T INNER JOIN 
       (Select ApplicantName, MAX(email) As [GoodEmail]
        FROM [COLOR=red]myTable[/color]
        Group By ApplicantName
        HAVING MAX(email) IS NOT NULL) Q
       ON T.ApplicantName = Q.ApplicantName

SET T.email = Q.GoodEmail

 

[lespaul]

that's what he gave you....did you try it?

you need to change every instance of MyTable in the SQL above to your actual tablename and make sure the field names match.

There is an alias for the first instance of your table T, the inner sub query is a correlate sub query that reference the alias T so that you can use the same table twice and get the update you require.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[frankis]

i'm lost!! where does Q come in?

Q ON T.ApplicantName = Q.ApplicantName

sorry guys i am a little thick.....

 

[Golom]

Q" and "T" are just aliases.

myTable T means that we are going to refer to the table myTable using the letter T elsewhere in this SQL statement.

(... SQL Sub Query ...) Q means that we are going to refer to fields coming from the subquery by prefixing them with Q.

So T.ApplicantName = Q.ApplicantName means that the ApplicantName field in myTable must be equal to the ApplicantName field coming from the subquery.

 

[frankis]

i've tried this:

UPDATE tblApplicant T INNER JOIN
(Select FirstName, Surname, JobTitle, MAX(email) As [GoodEmail]
FROM tblApplicant
Group By Surname
HAVING MAX(email) IS NOT NULL) Q
ON ((T.FirstName = Q.FirstName) AND (T.Surname=Q.Surname) AND (T.JobTitle= Q.JobTitle))

SET T.email = Q.GoodEmail;

but its coming up with this:
You tried to execute a query that does not include the specified expression <name> as part of an aggregate function.

 

[Golom]

You need

Group By FirstName, Surname, JobTitle

You must "Group By" every field in the SELECT that is not part of an aggregate function.

 

[PHV]

And if by chance you get a message similar to 'you must use an updatable query' :

UPDATE tblApplicant
SET email = DMax("email","tblApplicant","FirstName='" & [FirstName] & "' AND Surname='" & [Surname] & "' AND JobTitle='" & [JobTitle] & "'")
WHERE Trim(email & "") = ""

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[frankis]

Yes indeed PH, Operation must use an updatable query was what happened. Your solution worked a treat!

many thanks y'all