Uodating tables

[itguymike]

we are trying to update the operation types for all operation.resource_id=' qc testing', when we run the script below it updates all resources besides the the "qc testing" resource.

UPDATE OPERATION

SET
OPERATION_TYPE='QC TEST (TYPE1)',SETUP_HRS= '0',
RUN_TYPE= 'HRS/LOAD',
LOAD_SIZE_QTY= '99000',
RUN= '2',
MOVE_HRS= '166',
SETUP_COST_PER_HR = '0',
RUN_COST_PER_HR= '25',
RUN_COST_PER_UNIT= '0',
BUR_PER_HR_SETUP= '0',
BUR_PER_HR_RUN= '0',
BUR_PER_UNIT_RUN= '0',
BUR_PERCENT_RUN= '200'

FROM OPERATION
INNER JOIN WORK_ORDER ON WORKORDER_BASE_ID=BASE_ID
INNER JOIN PART ON PART_ID=ID

WHERE OPERATION.RESOURCE_ID='QC TESTING'
AND WORK_ORDER.STATUS='U'
OR WORK_ORDER.STATUS='R'
OR WORK_ORDER.STATUS='F'

any ideas?

 

[tcsbiz]

What this is doing is updating records that meet the conditions:

1. OPERATION.RESOURCE_ID='QC TESTING'
AND WORK_ORDER.STATUS='U'

2. OR WORK_ORDER.STATUS='R'
3. OR WORK_ORDER.STATUS='F'

So, your getting all records in 2 & 3.

Try

WHERE (OPERATION.RESOURCE_ID='QC TESTING'
 AND WORK_ORDER.STATUS IN('U', 'R', 'F')

Tom

 

[Olaf Doschke]

Boolean algebra needs parenthesis you want A AND (B OR C OR D), which is not the same as A AND B OR C OR D. Without the parenthesis the whole condition is also true, if just C or D are true.

Tom's change to IN is a nice way to shorten the OR conditions into one condition.

Bye, Olaf.