UPDATE specific records

[jay_elephant]

hi, i am tryin to update 6 specific records out of 400 odd. Problem is my statement is updating all 400 records.

UPDATE AM_X_422_2
SET
security_level =
(
SELECT
MAX(security_level)
FROM
(
SELECT
code_name,
LENGTH(code_name),
security_level,
mission_date
FROM
AM_X_422_2
ORDER BY
mission_date desc
)
WHERE
rownum < 11
)
WHERE
LENGTH(code_name) > 7
;


I want to update the security level to the highest security level for missions which are within the 10 most recent missions and where length of the first word of the mission code_name exceeds 7 characters.

[jay_elephant]

statement has been changed to this:
UPDATE AM_X_422_2
SET
security_level =
(
SELECT
security
FROM
(
SELECT
code_name,
LENGTH(code_name),
MAX(security_level) security,
mission_date
FROM
AM_X_422_2
ORDER BY
mission_date desc
GROUP BY
security_level
)
WHERE
rownum < 11
)
WHERE
LENGTH(code_name) > 7
;

getting a ORA-00907 error. something to do with the group function???

[skhanal]

You can't have aggregated and non aggregated columns in SELECT list. You are grouping by security_level but other columns are not grouped or aggregated.

[jay_elephant]

UPDATE AM_X_422_2 am
SET
am.security_level =
(
SELECT
lev
FROM
(
SELECT
max(am.security_level) lev
FROM
(
SELECT
name,
no,
am.security_level
FROM
(
SELECT
am.code_name name,
LENGTH(am.code_name) no,
am.security_level,
am.mission_date
FROM
AM_X_422_2 am
ORDER BY
am.mission_date desc
)
WHERE
rownum < 11
)
WHERE
LENGTH(name) > 7
)over
WHERE
over.lev= am.security_level)

changed the code which now brings up the ORA-01407 error, cannot update to null value. I did check the select statement which does return a max value

[skhanal]

With MAX function Oracle returns null record if there are no records matching your condition. For example

select max(dummy) from dual where dummy <>'X';

returns one row with null value. Check if similar thing is happening in your code.