Simple Access Update query

vpradmin
vpradmin used Ask the Experts™
on
I'm not sure why this update query returns thousands of records when I preview it in Access 2007 (and won't even finish executing if I run it) instead of a couple thousand like it's supposed to. I've done hundreds of queries like this before, and this query is not terribly complicated. Here is the SQL:

UPDATE Awards, FO SET Awards.Username = [FO].[Username]
WHERE [FO].[FullName] Like [Awards].[FullName] & "*";

Basically I just want to update the "Username" field in the "Awards" table with the "Username" field from the "FO" table where the "FullName" from the "Awards" table begins with the same beginning characters (last name and first name essentially) as the "FullName" from the "FO" table. So in other words the name "Smith,David" from the "Awards" table should match the name "Smith,David L." in the "FO" table (hence the "LIKE" operator), it should then get the "username" from the "FO" table and update the "username" field in the "Awards" table.

This is very straightforward and I'm not sure why it won't work. The "Awards" table only has about 5000 records and the "FO" table about 2000. However when you preview the results the records are in the hundreds of thousands, so it obviously isn't working correctly. Any suggestions?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015
Commented:
You've run into the Natural Join realm. Basically, every record is being combined with every other record in your database to form the hundreds of thousands of records you see. This occurs because you didn't specify a key relationship between the tables. Is there a field in each table that you could link together to make relate a row in one table to a row in another? At first guess, I'd say that's what your Username column is for. I only ask because I'm hoping there might be another column you could use. When you update a table using a join, you want to do something like the following:
UPDATE Awards INNER JOIN FO
       ON Awards.ForeignKeyColumnName = FO.PrimaryKeyColumnName
 
SET    Awards.[Username] = FO.[Username]
 
WHERE FO.[FullName] Like Awards.[FullName] & "*"
 
 
// ForeignKeyColumnName is the name of the column in Awards that if you queried FO using that value, it would return a row of information related to the entry in Awards

Open in new window

kaufmed is right on the reason for the number of records.
I presume that you don't have any other field that you can use to join the two tables.
What you can do is use your original where clause expression as the join expression.
Try this:
UPDATE Awards Inner Join FO On [FO].[FullName] Like [Awards].[FullName] & "*"
SET Awards.Username = [FO].[Username]


Author

Commented:
Yeah I suspected that it was doing a "natural join" though I never knew the term for it. I just don't understand WHY I need to do a join for this to work. I would NOT need to do a join if I was trying to do an exact match on the "FullName" field. For example this would work:

UPDATE Awards, FO SET Awards.Username = [FO].[Username]
WHERE [FO].[FullName] = [Awards].[FullName];

I have not defined a join here and this works. So why should using the "LIKE" operator and a wildcar make any difference? It should still evaluate to a true statement and execute...

MikeToole, I tried your UPDATE statement and it did the same thing as mine (did a natural join). I've decided that one thing I can do, though it will be a pain, is to create a primary key on the FO table by just taking the first several characters of the "FullName" and creating a foreign key on the other table with the same technique, and then doing a join between them.  Not the most elegant solution but its better than having to manually update the records in the Awards table.

If this works I'll give both of you guys points for your answers.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

UPDATE Awards, FO SET Awards.Username = [FO].[Username]
WHERE [FO].[FullName] = [Awards].[FullName];

It might not look like it, but this does do a join - the Where clause defines it.
This was the old way to specify Joins before the JOIN clause was introduced in the ANSI 92 SQL standard. You'll find a lot of Oracle SQL still coded this way

Author

Commented:
I would have given an "A" if you guys could have told me WHY my method doesn't work when you use the LIKE operator and a wilcard but DOES work when you use "=".
Mmm, I tried the Like and it didn't give me a natural join in the Select:

SELECT tmpEmployee.LName, Employees.[Last Name]
FROM tmpEmployee, Employees
WHERE Employees.[last name] Like [lname] & "*";

There were 9 rows in each of the two tables and 9 rows in the result set. So I changed it to an update:

UPDATE tmpEmployee, Employees SET tmpEmployee.LName = [Last Name]
WHERE (((Employees.[Last Name]) Like [lname] & "*"));

- and it worked!

So, why doesn't the your original do it? I'm afraid I don't know, but it's worthwhile starting with it as a Select if you want to continue experimenting.
(By the way, apologies for not spotting that your original had a Join in the Where clause)


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial