db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael J. Segel" <mse...@segel.com>
Subject Re: Select and null problem
Date Thu, 27 Oct 2005 04:07:53 GMT
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

Mime
View raw message