CODEWITHSUNDEEP
mysql
James C.
James C.

Reputation: 37

Where clause not working in Update - set query

I'm trying to update some columns by joining another table. I have a Y or N flag and I have different updates for those 2 groups. When I run 1 update for 1 group and specify the flag, it still updates the rows that I excluded:

update orders a 
set a.rec_qty_ind= (select SUM(received_qty) from detail b
where a.item_id = b.item_id
and b.mabd <= mabd + 13
and b.mabd >= mabd -2 
and dc_id = 6969
and is_legacy = 'Y'
group by a.item_id)
;

The is_legacy flag should make so that only the Y rows are updated but the N ones are getting updated as well. Anyone knows what's going on? Thanks!

Upvotes: 0

Views: 975

Answers (1)

Blue
Blue

Reputation: 22911

Your query is updating all rows, and setting it to the result of the query. From the docs:

If you update a column that has been declared NOT NULL by setting to NULL, an error occurs if strict SQL mode is enabled; otherwise, the column is set to the implicit default value for the column data type and the warning count is incremented. The implicit default value is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. See Section 11.7, “Data Type Default Values”.

When MySQL returns no rows, it still attempts to set a.rec_qty_ind to NULL. If you only want to update certain rows, you need to add additional logic outside of the query. Looking at it better formatted, you should see why this is happening:

UPDATE orders a 
SET a.rec_qty_ind = (
    SELECT SUM(received_qty)
    FROM detail b
    WHERE a.item_id = b.item_id
    AND b.mabd <= mabd + 13
    AND b.mabd >= mabd -2 
    AND dc_id = 6969
    AND is_legacy = 'Y'
    GROUP BY a.item_id
)

How about something like this?

UPDATE orders a
INNER JOIN (
    SELECT b.item_id, SUM(received_qty) AS recieved_sum
    FROM detail b
    WHERE a.item_id = b.item_id
    AND b.mabd <= mabd + 13
    AND b.mabd >= mabd -2 
    AND dc_id = 6969
    AND is_legacy = 'Y'
    GROUP BY a.item_id
) b ON a.item_id = b.item_id 
SET a.rec_qty_ind = b.recieved_sum

Upvotes: 1

Related Questions