Hello,

i need help with following update SQL:

update table1 a set a.IDPHNO = (select b.IRPHNO from table2 b where
a.IDSUNO = b.IRSUNO and b.IRSUNO <> '') where a.IDPHNO<> ''
-> Error: null values not allowed (ID SQL0407)

I also tried following:

update table1 a set a.IDPHNO = (select b.IRPHNO from
table2 b where a.IDSUNO = b.IRSUNO and b.IRPHNO <>'')
where exists
(select * from
table2 b where a.IDSUNO = b.IRSUNO and b.IRPHNO <>'')
-> more than one row (ID SQL0811)

Can someone help?
Thanks, Chatman

There is a NOT NULL constraint defined on IDPHNO column. You may add to the WHERE clause:
AND b.IRPHNO IS NOT NULL
Of course, I do not know what your business requirements are.

Another way, if you want to update all records in your select criteria, is to use a case statement so that if b.IRPHNO is null, you return a dummy value.

Hello, thanks for your feedback but i think this do not work to add NULL in query:

update table1 a set a.IDPHNO =
(
select b.IRPHNO from table2 b
where a.IDSUNO = b.IRSUNO and b.IRPHNO <> '' and b.IRPHNO is not NULL
)
where a.IDPHNO <> '' and a.IDPHNO is not NULL

Any idea?

If the subquery doesn't return any rows, your UPDATE statement will attempt to assign NULL to a.IDPHNO