Ahh perhaps i didnt explain very well my problem :
I have an error with this query :
insert into foo (id, name, date, remark) select 1234, 'a_text', null,
remark from foo where id = 1234;
And right here derby is not happy by the null value.
The same if a do this :
select 'test', null from foo;
Because i have a table where the date can be null.
better like that ?
Nicolas
Rajesh Kartha wrote:
Nicolas Dufour wrote:
Hi
I m trying to do a pretty simple insert :
insert into foo (fields ........) select value1, value2, ....,
field3, field5 from foo where id = x
Everything work until when a value is equal to NULL, i have this
message :
error: Encountered "null" at line ....
Ok sql has seen a null well good for it ... but why its an error !?
Thanks
Nicolas Dufour
Hi Nicolas,
Is there any stack trace, SQLState etc. ? Can you shed some light on
the table schema ? How many rows are you trying to insert ?
Based on your mail I tried a very very simple example in ij:
<sql>
connect 'jdbc:derby:testdb;create=true';
drop table tab1;
create table tab1 (id int, col1 char(1), col2 char(2), col3 char(3));
insert into tab1 values (1,'a','aa','aaa');
insert into tab1 values(2,'b','bb','bbb');
insert into tab1(id,col2,col3) values (3,'cc','ccc');
insert into tab1(id,col2,col3) values (4,'dd','ddd');
insert into tab1(id,col1,col2) values (5,'e','ee');
select * from tab1;
insert into tab1(id, col1, col2, col3) select id, col1, col2, col3
from tab1;
select * from tab1;
</sql>
it worked fine for me and the final select gives me:
ID |COL1|COL2|COL3
--------------------------
1 |a |aa |aaa
2 |b |bb |bbb
3 |NULL|cc |ccc
4 |NULL|dd |ddd
5 |e |ee |NULL
1 |a |aa |aaa
2 |b |bb |bbb
3 |NULL|cc |ccc
4 |NULL|dd |ddd
5 |e |ee |NULL
10 rows selected
Correct me, is this similar to what you are trying to do ? Adding the
where clause like yours also works fine.
I am using Derby 10.1.1.0 - (225786).
Regards,
Rajesh
begin:vcard
fn:Nicolas Dufour
n:Dufour;Nicolas
org:NeoMetSys;Linux
adr:;;7 rue du theatre;Massy;;91300;France
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:+1 973 379 3522
x-mozilla-html:FALSE
url:http://www.neometsys.fr/
version:2.1
end:vcard
