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
--
Michael Segel
Principal
MSCC
(312) 952-8175
