Im trying to copy a part of a row into another one : some fields are
filled by the program (like the null which is a date), some fields are a
copy of the original row. That's why I have values and field names in
the select to insert this new row.
Besides when you do this :
select 'aaa' from foo;
You have as many of 'aaa' as rows in foo.
Michael J. Segel wrote:
On Wednesday 26 October 2005 17:11, Nicolas Dufour wrote:
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.
Ok, this really doesn't make sense.
First, lets format your statement a little better...
INSERT INTO foo (id, name, date, remark)
SELECT 124, 'a_text', null, remark
FROM foo
WHERE id = 1234;
Again, what is it that you're trying to do?
Copy the row? Did you mean this? :
INSERT INTO foo (id, name, date, remark)
SELECT id, name, date, remark
FROM foo
WHERE id=1234;
Now if date is NULL, then it will insert a null value in your new row.
Note that if id is set as a unique or identity column this query will fail
since the id already exists in the table.
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
Again in your second test, are you trying to select only those rows where the
date is null?
Sorry but I guess I'm missing something...
Going back to your original statement:
Try:
INSERT INTO foo
SELECT *
FROM foo
WHERE id = ?;
This should work.
HTH
-G
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
