Null values not allowed in UPDATE

[gregwga50]

When I run the following SQL update

update FILE1 a
set IJJDSC = (select distinct DJJDSC from FILE2
where DJSTMP = 20140628160729
and DJSTMP = a.ijstmp
and substring(DJJDSC,1,23) = substring(a.iJJDSC,1,23) )

I get Null values not allowed in column or variable IJJDSC

There don't appear to be any null values in DJJDSC and an exception join between FILE1 anf FILE2 produces nothing so I am not sure why the subquery produces null

[DeadManWalks]

Re: Null values not allowed in UPDATE

slap a value() around the entire subselect.

[DeadManWalks]

Re: Null values not allowed in UPDATE

You get a null value when it is not found. So you are going to set the fileld to null when the subselect is not found.

[gregwga50]

Re: Null values not allowed in UPDATE

Yes, but for every row in FILE2 that has DJSTMP = 20140628160729, there is a row in FILE1 that has IJSTMP = 20140628160729 and the 1st 23 positions of journal description in FILE2 equal to the 1st 23 positions of journal description in FILE1

[arrow483]

Re: Null values not allowed in UPDATE

Try this

update FILE1 a
set IJJDSC = coalesce((select distinct DJJDSC from FILE2
where DJSTMP = 20140628160729
and DJSTMP = a.ijstmp
and substring(DJJDSC,1,23) = substring(a.iJJDSC,1,23) ), 'ERROR')

If the value sets to "ERROR", then the select is failing.

[tomliotta]

Re: Null values not allowed in UPDATE

You might also want to put a WHERE clause on the UPDATE statement to match the limited set of rows being returned from the SELECT. By the look of it, there will be rows UPDATEd that won't have any matches from the SELECT.

[DeadManWalks]

Re: Null values not allowed in UPDATE

You got the null message based on if the statement could produce a null, not if there were hits or not.

[Rocky]

Re: Null values not allowed in UPDATE

Evidently there aren't always records in FILE2 where DJSTMP = 20140628160729 and DJSTMP = a.ijstmp and substring(DJJDSC,1,23) = substring(a.iJJDSC,1,23) finds a record. When this happens the subselect returns a null. If field IJJDSC doesn't allow a null it will give you a message that a null value isn't allowed. Use the Coalesce function ***OR**** add a where clause to the update to ensure that there is a match.

[tomliotta]

Re: Null values not allowed in UPDATE

A difference is that a WHERE clause can limit which rows are UPDATEd, while COALESCE() without UPDATE...WHERE will UPDATE every row with either a SELECTed value or the alternative value.

COALESCE() would be appropriate if every row in FILE1 should be UPDATEd regardless of a match with FILE2. A WHERE clause for the UPDATE is appropriate if only rows in FILE1 that match with FILE2 should get the matching value.

It'd be nice if there was a shorthand way to get SQL to understand the intent. Until that's available, duplicate WHERE clauses for inner subSELECTs and any outer statements are often needed, or an alternative such COALESCE() handles the extras.

[B.Hauser]

Re: Null values not allowed in UPDATE

The correct syntax for an update statement based on data from a different table without updating each row within the base table is:

update Table1 a                                       
   set a.Col1 = (Select Distinct b.Colx                
                         from  Table2 b              
                         where     a.Key1 = b.Key1
                               and a.key2 = b.Key2 
                               ...
                               and a.KeyN = b.KeyN)
   where Exists (Select * from Table2 c              
                    Where     a.Key1 = c.Key1
                          and a.Key2 = c.Key2
                          ...
                          and a.KeyN = c.KeyN)

In this way your update statement must be converted as follows:

update FILE1 a
   set IJJDSC = (select distinct DJJDSC from FILE2
                    where     DJSTMP = 20140628160729
                          and DJSTMP = a.ijstmp
                          and substring(DJJDSC,1,23) = substring(a.iJJDSC,1,23))
Where exists (Select 1
                from File2 c
                where     c.DJSTMP = 20140628160729
                      and c.DJSTMP = a.ijstmp
                      and substring(c.DJJDSC,1,23) = substring(a.iJJDSC,1,23))

Birgitta

[TedHolt]

Re: Null values not allowed in UPDATE

I have better luck with MERGE when updating one table from another.

http://www.itjungle.com/fhg/fhg040313-story02.html

[DeadManWalks]

Re: Null values not allowed in UPDATE

Merge is available @ 7.1 +