Query problem-getting wrong result when a condition or a set of data included

I have bug in my query, but I have no idea what happen. So there is 3 tables.

table 1

name   grade  min  max
a
b
c
d
e

table 2

fullname   name  min   max
a          a123  1     10
bbbb       b     2     20
c          cccc  3     30
d          dd    1     10
E          Ed    2     20

table 3

value    grade
25        A
15        B
5         C

my goal is using name, show the grade of the name( the max > value in table 3).

for example, c has 30 in max, it should have A grade, instead of B and C.

Also, the name usually is the fullname in table 2, but sometime it is name in the table2(like b)(here is one of bugs). That’s how the table look like, I can’t change it.

if I am not include the checking table 1.name = table 2.name . no bug at all, but cannot get grade for b

if i include the table 1.name = table 2.name.then, it has problem

for the query of matching the grade, it is like(assume get the min and max from table 2 before)

update table1
set table1.grade = table3.grade
from table1 inner join table3 
on table1.max > table3.value

All the cases are include the checking table 1.name = table 2.name

case 1: the grade will equal = C for all data if there is some data inlcude.

for example, in table1, if I am not include E, then everything is fine.

but if I include E, will get C grade for all records.

case 2: if I run the query for all data at the same time, the result goes wrong. it work fine if I update the record one by one., for example, i add one more condition in update query

update table1
set table1.grade = table3.grade
from table1 inner join table3 
on table1.max > table3.value and fullname='c'

after getting wrong result, i add the condition and run it again, then c will get grade ‘A’ instead of ‘C’. but if I remove the condition and run the query again. c will get grade ‘C’ again.

case 3: there is no problem when I only run the set of data that will cause case 1 problem independently. but if I put the data together, It cases problem.

That is all cases. I don’t know what cause the problem. Please help

The result should be: table 1

name   grade  min  max
a        C     1    10
b        B     2    20
c        A     3    30
d        C     1    10
e        B     2    20

If I remove table1.name = table2.name, result will be

table 1

name   grade  min  max
a        C     1    10
b       null  null  null
c        A     3    30
d        C     1    10
e        B     2    20

with table1.name = table2.name, result will be

table 1

name   grade  min  max
a        C     1    10
b        C     2    20
c        C     3    30
d        C     1    10
e        C     2    20

with table1.name = table2.name but remove e , result will be

table 1

name   grade  min  max
a        C     1    10
b        B     2    20
c        A     3    30
d        C     1    10

with table1.name = table2.name but only for e,result will be

name   grade  min  max
e        B     2    20

those situation happen when I run the update query for whole table. there is no problem with table1.name = table2.name if I update each row one by one.

Answer

At the very least, you should avoid the way you update the table. You should be carefull with joins on update. If you happen to have more than one value for the same row, the result is not deterministic. Better use this form:

update table1
set table1.grade = (SELECT TOP 1 table3.grade FROM table3
                    WHERE table3.value < table1.max
                    ORDER BY table3.value DESC)

Advertisement

Answer