Why is this UPDATE statement updating every record?

Question

I have an UPDATE statement that's intended to update a status field for a limited number of records. Here's the statement:

UPDATE warehouse_box
SET warehouse_box_status_id = wbsv.warehouse_box_status_id
FROM
 warehouse_box_status_vw wbsv INNER JOIN
 pallet_warehouse_box pwb ON wbsv.warehouse_box_id = pwb.warehouse_box_id INNER JOIN
 routing_shipment_pallet rsp ON pwb.pallet_id = rsp.pallet_id
WHERE
 rsp.date_removed IS NULL
 AND pwb.date_unpalletized IS NULL
 AND rsp.routing_shipment_id = 100002

The intended result is for the 6 records that match the WHERE clause to have their statuses updated. What I'm seeing though is that all 200,000+ records are updated. It's as though the WHERE clause is being completely ignored.

Can anyone help shed some light on this for me?

Answer 1

You don't appear to be selecting on warehouse_box in the WHERE and INNER JOINs -- only from other tables...

Answer 2

You do not have the table warehouse_box included in the FROM clause. If you add warehouse_box and join it to warehouse_box_status it should solve your problem.

Answer 3

What happens when you perform a SELECT with the same FROM and WHERE clauses?

• If you get all 200K rows, there are things you can do to track down the problem from there.

• If you get only the 6 records you want to update, it's time to start looking for triggers that might be updating the whole table.

EDIT: Looks like sharper eyes have already found the problem.