db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nicolas Dufour <nicolas.duf...@neometsys.fr>
Subject Re: Select and null problem
Date Thu, 27 Oct 2005 12:49:34 GMT
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
>>>       
>
>   


Mime
View raw message