Avatar of sagarh
Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

SQL Query Issue

Hi I have the following query that I am writing to create a stored procedure:-

IF OBJECT_ID('scheme.upsupdate','U') IS NOT NULL
    DROP TABLE scheme.upsupdate

select order_no,ship_cost into scheme.upsupdate
from scheme.upsexport where void!='Y' and updated!='Y'

update scheme.opdetm
set cost_of_sale = (select ship_cost
from scheme.upsupdate where opdetm.order_no=upsupdate.order_no)

update scheme.upsexport set updated ='Y' where order_no = (select order_no from scheme.upsupdate)

I am having an issue with the first update in that I get an error :-

Error: Cannot insert the value NULL into column 'cost_of_sale', table 'demo.scheme.opdetm'; column does not allow nulls. UPDATE fails.

Is there any way round this - I cannot change the database tables as thet would invalidate support for our accounts package....

Thanks for the help in advance!
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]

8/22/2022 - Mon