oracle updata为null,Oracle “Cannot update to NULL”

问题

I have this query on Oracle 10g:

UPDATE "SCHEMA1"."CELLS_GLIST"

SET ("GLIST_VALUE_ID", "USER_ID", "SESSION_ID") = (

SELECT "GLIST_VALUE_ID", 1 AS "USER_ID", 123456 AS "SESSION_ID"

FROM "SCHEMA1"."GLISTS_VALUES_UOR"

WHERE ("UOR_ID"=3)

AND ("GLIST_ID"=67)

AND ("GLIST_VALUE_DESC" = (

SELECT "GLIST_VALUE_DESC"

FROM "BMAN_TP1"."GLISTS_VALUES_UOR"

WHERE ("UOR_ID"=3)

AND ("GLIST_VALUE_ID"="CELLS_GLIST"."GLIST_VALUE_ID")

))

)

WHERE EXISTS (......)

It keeps saying ORA-01407: cannot update ("SCHEMA1"."CELLS_GLIST"."SESSION_ID") to NULL

"SESSION_ID" is obviously Not Nullable, but I'm actually passing a value to that field, so I do not understand the problem.

回答1:

From your comments, I read that you seem to want to write a default record to your target table, in case the subquery doesn't return any records. So the correct way to phrase your query would be using a MERGE statement as such:

MERGE INTO "SCHEMA1"."CELLS_GLIST" dst

USING (

-- rephrase your subquery here. This is your "merge data source". The number

-- of records returned in this subquery will correspond to the number of

-- affected records in dst

) src

ON (

-- the missing exists condition here. Everytime this condition matches a record

-- between dst and src, an UPDATE is performed. Otherwise, an INSERT is

-- performed

)

WHEN MATCHED THEN UPDATE

SET dst."GLIST_VALUE_ID" = src."GLIST_VALUE_ID"

WHEN NOT MATCHED THEN INSERT ("GLIST_VALUE_ID", "USER_ID", "SESSION_ID")

VALUES (NULL, 1, 123456);

This is just to give you an idea. I'm not quite sure what you're trying to achieve in detail, so I omitted the subqueries and conditions

回答2:

I found this query also to work:

UPDATE "BMAN_TP1"."CELLS_GLIST"

SET "GLIST_VALUE_ID" = (

SELECT "GLIST_VALUE_ID"

FROM "BMAN_TP1"."GLISTS_VALUES_UOR"

WHERE ("UOR_ID"=3)

AND ("GLIST_ID"=67)

AND ("GLIST_VALUE_DESC" = (

SELECT "GLIST_VALUE_DESC"

FROM "BMAN_TP1"."GLISTS_VALUES_UOR"

WHERE ("UOR_ID"=3)

AND ("GLIST_VALUE_ID"="CELLS_GLIST"."GLIST_VALUE_ID")

))

),

"SESSION_ID" = 123456,

"USER_ID" = 1

WHERE EXISTS (......)

But, it performs really really fast... I doubt I'm missing something...

来源：https://stackoverflow.com/questions/11523213/oracle-cannot-update-to-null