45701

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 <strong>ORA-01407: cannot update ("SCHEMA1"."CELLS_GLIST"."SESSION_ID") to NULL</strong>

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

Answer1:

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

Answer2:

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...

Recommend

  • Oracle SQL - Pivot table rows to column and use sub query in pivot
  • Date of max id: sql/oracle optimization
  • How to mock springSecurityService in unit tests using grails
  • Mapping ManyToMany with composite Primary key and Annotation:
  • Converting datatype Char to Nvarchar
  • Return null in boolean to checkbox state converter in XAML
  • How do I check if System::Collections:ArrayList is empty / nullptr / null?
  • Creating a C++ function that calls other Lua function
  • Multiple Left Join LINQ-to-entities
  • What does “t” refer to in this SQL?
  • How to use function wrapper in mustache.php?
  • Do I need to reset a Perl hash index?
  • Installing Apache MyFaces 2 on WildFly 8.2.0
  • Use of this Javascript
  • Using jQuery closest() method with class selector
  • Email format validation in mvc3 view
  • Retrieving value from sql ExecuteScalar()
  • C# - Is there a limit to the size of an httpWebRequest stream?
  • Array.prototype.includes - not transformed with babel
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • How to add date and time under each post in guestbook in google app engine
  • Join two tables and save into third-sql
  • How to model a transition system with SPIN
  • Statically linking a C++ library to a C# process using CLI or any other way
  • ORA-29908: missing primary invocation for ancillary operator
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • SQL merge duplicate rows and join values that are different
  • KeystoneJS: Relationships in Admin UI not updating
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • Hits per day in Google Big Query
  • coudnt use logback because of log4j
  • How does Linux kernel interrupt the application?
  • LevelDB C iterator
  • Can't mass-assign protected attributes when import data from csv file
  • Checking variable from a different class in C#
  • How can i traverse a binary tree from right to left in java?
  • Converting MP3 duration time
  • git trying to push non-existent file … after clearing cache