morinia
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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