Update and Select Query results don't agree

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an update query the SQL appears below.

UPDATE Employees SET Employees.Opportunities = 9
WHERE (((Employees.Employment_Length)>12) AND ((Employees.Normal_Shift)="2"
Or (Employees.Normal_Shift)="3")) OR (((Employees.Employment_Length)>12) AND
((Employees.Normal_Shift) Like "2%")) OR (((Employees.Employment_Length)>12)
AND ((Employees.Normal_Shift) Like "3%"));


It wants to update 52 records. When I change it to a select query, It
selects 100 records which I believe is correct and I want it to do. What am
I missing?
 
Let it perform the update, then analyze the records that didn't get updated.
See why they fail to meet the criteria.
 
If I let it update the 52 records, they update. When I look at the records
that didn't get updated but should have, there appears to be absolutely no
reason why they didn't get updated.

I go back to my original statement. If a select query selects all the
desired records then why doesn't the same query criteria perform the update?
All I do is to change the query type from Select to Update. SQL for the
Query is exactly the same except for the word SEKECT instead of UPDATE.
Select Yields 100 records, update only wants to update 52. Normal shift of 2
and 3 gets updated, 2A, 2B, 3A and 3B don't get updated, but they do get
selected. What gives? Doesn't make any sense to me at all.


SELECT Employees.Opportunities
FROM Employees
WHERE (((Employees.Normal_Shift)="2" Or (Employees.Normal_Shift)="3") AND
((Employees.Employment_Length)>12)) OR (((Employees.Normal_Shift) Like "2%")
AND ((Employees.Employment_Length)>12)) OR (((Employees.Normal_Shift) Like
"3%") AND ((Employees.Employment_Length)>12));

UPDATE Employees SET Employees.Opportunities = 9
WHERE (((Employees.Normal_Shift)="2" Or (Employees.Normal_Shift)="3") AND
((Employees.Employment_Length)>12)) OR (((Employees.Normal_Shift) Like "2%")
AND ((Employees.Employment_Length)>12)) OR (((Employees.Normal_Shift) Like
"3%") AND ((Employees.Employment_Length)>12));
 
Some of the records are below. by the way, the select query selects these
records just fine.

Clock# FirstName LastName MI Hourly Probationary YTD_Absence Monthly_Accruement_Rate Previous_Vacation_Balance Current_Vacation_Hours Seniority_Date Benefit_Date Department Skip_Accrual Union_ Default_Hours_Worked Class Employment_Length Manual_Vacation_Amount Additional_Hours_Added Normal_Shift Opportunities_Granted Opportunities Supervisor RemoveFlag upsize_ts
4074 ROGER WAGENIUS P -1 0 4 13.333 67.022 80.355 2/16/1981 2/16/1981 A326 0 0 10 30 297 0 27 3A 0 0 4256 0
4179 DAN JOHNSON E -1 0 0 13.333 168.746 182.079 4/9/1984 4/9/1984 A311 0 0 10 30 260 0 0 3A 7 7 4256 0
4367 JIM CHUTE A -1 0 6.5 13.333 66.732 80.065 5/16/1988 5/16/1988 A326 0 0 10 30 210 0 8 3A 0 0 4256 0
4370 DARYL ANDERSON L -1 0 1.5 13.333 84.666 97.999 5/23/1988 5/23/1988 A310 0 0 10 10 210 0 0 3A 0 0 4256 0
4372 TIM MCKELLAR K -1 0 1 6.667 34.563 41.23 7/22/2002 7/22/2002 A311 0 0 10 40 40 0 0 3A 8 8 4256 0
1758 TEDDY HAGEMAN C -1 0 3.5 16.667 86.8760000000001 103.543 9/27/1973 9/27/1973 A326 0 0 10 30 386 0 0 3A 7 7 4256 0
1832 WAYNE SULLIVAN T -1 0 3 16.667 91.145 107.812 1/19/1976 1/19/1976 A326 0 0 10 10 358 0 0 3A 5 5 4256 0
4400 MIKE LINDUS J -1 0 3 13.333 97.041 110.374 10/23/1989 6/5/1989 A326 0 0 10 30 198 0 0 3A 3 3 4256 0
4587 TERRY LARSON A -1 0 0 10 102.009 112.009 10/10/1994 10/10/1994 A311 0 0 10 40 134 0 0 3A 1 1 4256 0
4609 RODNEY ERICKSON C -1 0 3.5 10 50.342 60.342 11/7/1994 11/7/1994 A314 0 0 10 30 133 0 0 3A 3 3 4256 0
4611 ROBBI KOSTER K -1 0 4.5 10 0.342 0.341999999999999 11/14/1994 11/14/1994 A326 -1 0 10 70 133 0 16 3A 9 9 4256 0
4728 MICHAEL TAYLOR D -1 0 0 10 56.329 66.329 5/12/1997 5/12/1997 A314 0 0 10 30 103 0 0 3A 2 2 4256 0
4734 SCOTT MARTINI A -1 0 1 10 85.745 95.745 6/2/1997 6/2/1997 A326 0 0 10 40 102 0 0 3A 8 8 4256 0
4754 DALE ROWELL L -1 0 0.5 10 62.329 72.329 10/13/1997 10/13/1997 A314 0 0 10 30 98 0 0 3A 2 2 4256 0
4755 JAMES FRISBEE S -1 0 0 10 64.497 74.497 10/14/1997 10/14/1997 A310 0 0 10 50 98 0 0 3A 8 8 4256 0
4784 BARB WUOLLET A -1 0 4 10 58.663 68.663 2/2/1998 2/2/1998 A326 0 0 10 70 94 0 0 3A 0 0 4256 0
4788 TOM HOLMQUIST A -1 0 2.5 10 53.663 63.663 2/6/1998 2/6/1998 A314 0 0 10 40 94 0 0 3A 2 2 4256 0
4789 MARGO PETERSON R -1 0 6 10 50.331 60.331 2/13/1998 2/13/1998 A310 0 0 10 50 94 0 0 3A 0 0 4256 0
4794 BRENDA JOHNSON L -1 0 6.5 10 58.335 68.335 2/27/1998 2/27/1998 A326 0 0 10 50 93 0 13 3A 0 0 4256 0
4871 CARSON CHRISTIAN -1 0 0.5 6.667 68.445 75.112 6/7/2004 6/7/2004 A326 0 0 10 50 18 0 0 3A 2 2 4256 0
 
Guess the problem is solved. I was using the % sign as a wild card in the
query criteria. Don't know what possessed me to do so, but interestingly
enough, it worked in the select query. However didn't work in the update
query. I changed it to a * in the update query and it works as expected.
Thanks for your help.
 
Post an example record of one that was not updated. Maybe there is
something you are missing.??
 
Back
Top