CODEWITHSUNDEEP
sqldb2-400
dileep gare
dileep gare

Reputation: 21

Iseries SQL update

Table 1 

Column1  Column2
aa         12
bb         12
cc         12
aa         12
bb         12

Table 2

Column1  Column2
aa         12
bb         13
cc         14
aa         15
bb         16

Now, using Sql query I need to update table1.column2 with table2.column2 based on table2 to column1 and table1.column1 records

update Table1 a set a.Column2 = ( select b.Column2from Table2
b where A.column1= b.column1)

The above query raises the error:

Null values not allowed in column or variable.

I'm trying to copy data between 2 different systems.

Upvotes: 2

Views: 1896

Answers (2)

Charles
Charles

Reputation: 23823

In addition to @jarih's answer..

If your on a currently supported release, ie. 7.1 or higher...

You could use MERGE

merge into table1 A
using (select column1, column2 from table2) as B
on a.column1 = b.column1
when matched then
 update set a.column2 = b.column2

Upvotes: 1

jarlh
jarlh

Reputation: 44805

Seems like there are table1 rows without any matching table2 row. In that case the sub-query returns NULL. And I guess table1.column2 doesn't allow NULL's?

Add a WHERE clause to only update rows with matches in table2.

update Table1 a set a.Column2 = (select b.Column2 from Table2 b
                                 where A.column1= b.column1)
where exists (select 1 from Table2 b2
              where A.column1= b2.column1)

Upvotes: 3

Related Questions