vpradmin
asked on
Simple Access Update query
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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)
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)
ASKER
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.