CODEWITHSUNDEEP
sqloracle-databaseselect
Cmd_Keen
Cmd_Keen

Reputation: 23

SELECT not working in UPDATE (Oracle)

I want to do an UPDATE in Oracle SQL Developer and have following Code:

UPDATE DATABASE_1 
   SET  COLOR =        CASE WHEN COLOR IS NULL THEN '100'
                            WHEN COLOR = '100' THEN '100' ELSE COLOR      END,
        COLOR_UNIT  =  CASE WHEN COLOR_UNIT IS NULL THEN '%'
                            WHEN COLOR_UNIT = '' THEN '%' 
                            WHEN COLOR_UNIT = ' ' THEN '%' ELSE COLOR_UNIT END
WHERE ID IN    (SELECT t.ID
                FROM DATABASE_1 t
                JOIN DATABASE_2 s ON t.ID = s.ID AND t.SEQUENCE = s.SEQUENCE
                WHERE s.CRITERIA = 'HIGH' AND t.SEQUENCE = (SELECT MAX (te.SEQUENCE) FROM DATABASE_1 te WHERE te.ID = s.ID));

When I run the SELECT

SELECT t.ID
FROM DATABASE_1 t
JOIN DATABASE_2 s ON t.ID = s.ID AND t.SEQUENCE = s.SEQUENCE
WHERE s.CRITERIA = 'HIGH' AND t.SEQUENCE = (SELECT MAX (te.SEQUENCE) FROM DATABASE_1 te WHERE te.ID = s.ID)

by itself, it gives me the correct result (as expected).

Thing is, once I run the whole UPDATE Statement, the AND t.SEQUENCE = (SELECT MAX (te.SEQUENCE) FROM DATABASE_1 te WHERE te.ID = s.ID) part just gets ignored and it subsequently updates more rows (every entry which matches given ID and is true for s.CRITERIA = 'HIGH').

Upvotes: 1

Views: 412

Answers (1)

Boneist
Boneist

Reputation: 23588

I'm not surprised that your query is matching to more rows than you're expecting - assuming you're only wanting to update the row with the latest sequence per id.

Your subquery is essentially doing a check that for each id, there's a row with a sequences that matches the max sequence value. That's always going to be true as long as there's at least one row for each id in the table!

Instead, what I think you're after is:

UPDATE DATABASE_1 
   SET  COLOR =        CASE WHEN COLOR IS NULL THEN '100'
                            WHEN COLOR = '100' THEN '100' ELSE COLOR      END,
        COLOR_UNIT  =  CASE WHEN COLOR_UNIT IS NULL THEN '%'
                            --WHEN COLOR_UNIT = '' THEN '%'  -- totally irrelevant; there is no such thing as an empty string in Oracle (it is treated as NULL). Remove this.
                            WHEN COLOR_UNIT = ' ' THEN '%' ELSE COLOR_UNIT END
WHERE (ID, SEQUENCE) IN (SELECT t.ID, t.sequence
                         FROM DATABASE_1 t
                         JOIN DATABASE_2 s ON t.ID = s.ID AND t.SEQUENCE = s.SEQUENCE
                         WHERE s.CRITERIA = 'HIGH' AND t.SEQUENCE = (SELECT MAX (te.SEQUENCE) FROM DATABASE_1 te WHERE te.ID = s.ID));

Upvotes: 1

Related Questions