Link to home
Start Free TrialLog in
Avatar of morinia
moriniaFlag for United States of America

asked on

Error trying to do insert on table

Experts,

I am new to the SQL DBF platform.  I am trying to update a table with a rate.  I created a column

, 0 as rate

When trying to update rate using query below

UPDATE #1 D
      SET RATE = (SELECT IPRS_RATE FROM #DISTINCT_PRICE       P                    
                  WHERE TRIM(D.IPCD_ID) = TRIM(P.IPCD_ID)
                  AND P.IPRS_RATE IS NOT NULL)

I get this error

Error(21) ASA Error - 1002005: Tried to insert a null value into a non-null field RATE on row 98595

Can someone tell me how to rectify this issue by field definition or modification to update query?
Avatar of Camillia
Camillia
Flag of United States of America image

That error means your data has a null value in the result column and RATE column doesn't allow null.

Run the query by itself and see the values. Is there a null column?

SELECT IPRS_RATE FROM #DISTINCT_PRICE       P                    
                  WHERE TRIM(D.IPCD_ID) = TRIM(P.IPCD_ID)
                  AND P.IPRS_RATE IS NOT NULL
ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of morinia

ASKER

julianH

I tried the update above and I get Invalid data type comparison in predicate

Camilla,

The file does not appear to have nulls when I do the select.  When I try to select iprs_rate  is null I get nothing.
What database are you using?
Sql  server
Sorry.  Mistook this for my own ticket. On my phone and can't delete my previous comment for some reason.