I have a table like this, where, suppose for the sake of an example, NAME
is a unique identifier.
NAME AGE VALUE
Jack Under 65 3
Jack 66-74 5
John 66-74 7
John Over 75 9
Gill 25-35 11
Some NAME
s have more than one AGE
, which is undesirable, as this is due to dirtiness of the data.
My aim is to update the duplicates only to have one AGE
within each NAME
. The desired output is thus:
NAME AGE VALUE
Jack Under 65 3
Jack Under 65 5
John 66-74 7
John 66-74 9
Gill 25-35 11
Something like this UPDATE statement should work, but it doesn't.
UPDATE table t1
SET t1.age=MAX(t1.age)
WHERE EXISTS (SELECT COUNT(t2.AGE)
FROM table t2
WHERE t1.NAME=t2.NAME
GROUP BY t2.NAME
HAVING COUNT(t2.AGE) > 1)
SQL Error: ORA-00934: group function is not allowed here
Second issue
Even if I got the above statement to work, there is a second issue. The idea there is to use the MAX
(or MIN
) function on strings to set the same value for all repeats within a group.
But unfortunately, this too would not quite work as desired. For consistency, ideally an age would default to the lowest age group. But because MAX/MIN
compare alphabetic order on strings, this would give, e.g.:
- "66-74" and "Under 65" => MAX="Under 65" -- Lowest
- "66-74" and "Over 75" => MAX="Over 75" -- Highest
There are only four age groups, would it be possible to specify a custom order?
- NB1: I am using Oracle SQL.
- NB2: I do not mind if there is a way to achieve the result using a SELECT instead of an UPDATE statement.
Reproducible example
SELECT 'Jack' as NAME, 'Under 65' as AGE, 3 as VALUE from dual
UNION ALL
SELECT 'Jack' as NAME, '66-74' as AGE, 5 as VALUE from dual
UNION ALL
SELECT 'John' as NAME, '66-74' as AGE, 7 as VALUE from dual
UNION ALL
SELECT 'John' as NAME, 'Over 75' as AGE, 9 as VALUE from dual
UNION ALL
SELECT 'Gill' as NAME, '25-35' as AGE, 11 as VALUE from dual