Firebird

> -----Original Message-----
> From: firebird-devel-bounces@...
> [mailto:firebird-devel-bounces@... Behalf Of LacaK
> Sent: Jueves, 26 de Julio de 2007 4:52
>
> 3. version numbering why 2.1 and not 2.5 ?

Because v1 was C, v1.5 was C++ == huge change.
v2.1 is a set of enhancements to v2, but there may be more in the future,
who knows.

C.

Claudio Valderrama C. wrote:
> 
> Because v1 was C, v1.5 was C++ == huge change.

Plus many files were renamed, new configuration system, etc.
FB2 contained less major changes but it introduced ODS11.
v2.1 does nothing of the above, hence the version number.


Dmitry

> 2. UPDATE <multiple column assignment>
> Are there plans or intention extent UPDATE statement to :
> UPDATE <table>
> SET ( col1 ,col2 ) = (val1,val2) | ROW (val1[,val2]) | (<query expression>)
>   
Which records of <query expression> should be used for updating in this 
case?

LacaK wrote:
> 
> 1. SELECT ... INTO ... FROM ...
> SQL:2003 (INTO is before FROM):
>   SELECT [ <set quantifier> ] <select list>
>   INTO <select target list>
>   <table expression>
> 
>   select f into :v from t;
> 
> FB (INTO AFTER SELECT, does not allow into before from):
> 
>   select f from t into :v ;
> 
> I know, it is only detail, because it does not affect functionality, ... 
> consider please if it is interesting for you or not

Tracker is awaiting you :-)

> 2. UPDATE <multiple column assignment>
> Are there plans or intention extent UPDATE statement to :
> UPDATE <table>
> SET ( col1 ,col2 ) = (val1,val2) | ROW (val1[,val2]) | (<query expression>)

This is a generic feature (row value constructors) that has nothing to 
do with UPDATE. Consider also WHERE (fld1, fld2) = (1, 2) etc. Almost 
everything in the syntax should be extended.


Dmitry

Roman Simakov  wrote:
>> 2. UPDATE <multiple column assignment>
>> Are there plans or intention extent UPDATE statement to :
>> UPDATE <table>
>> SET ( col1 ,col2 ) = (val1,val2) | ROW (val1[,val2]) | (<query expression>)
>>   
> Which records of <query expression> should be used for updating in this 
> case?
> 
Template is not complete, look :

UPDATE <table>
SET ( col1 ,col2 ) = (val1,val2) | ROW (val1[,val2]) | (<query expression>)
WHERE [<search confition>]

Example:

UPDATE t1
SET (col1,col2)=(select sum(col3), sum(col4) from t2 where t2.k=t1.k)
WHERE ...

> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems?  Stop.
> Now Search log events and configuration files using AJAX and a browser.
> Download your FREE copy of Splunk now >>  http://get.splunk.com/
> Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
>

LacaK wrote:
> Roman Simakov  wrote:
>   
>>> 2. UPDATE <multiple column assignment>
>>> Are there plans or intention extent UPDATE statement to :
>>> UPDATE <table>
>>> SET ( col1 ,col2 ) = (val1,val2) | ROW (val1[,val2]) | (<query expression>)
>>>   
>>>       
>> Which records of <query expression> should be used for updating in this 
>> case?
>>
>>     
> Template is not complete, look :
>
> UPDATE <table>
> SET ( col1 ,col2 ) = (val1,val2) | ROW (val1[,val2]) | (<query expression>)
> WHERE [<search confition>]
>
> Example:
>
> UPDATE t1
> SET (col1,col2)=(select sum(col3), sum(col4) from t2 where t2.k=t1.k)
> WHERE ...
>   
"select sum(col3), sum(col4) from t2 where t2.k=t1.k" can return 
multiple records! What value should be assigned to col1 and col2 in 
separate record of t1?

> >>> 2. UPDATE <multiple column assignment>
> >>> Are there plans or intention extent UPDATE statement to :
> >>> UPDATE <table>
> >>> SET ( col1 ,col2 ) = (val1,val2) | ROW (val1[,val2]) | (<query
expression>)
> >>>
> >>>
> >> Which records of <query expression> should be used for updating in this
> >> case?
> >>
> >>
> > Template is not complete, look :
> >
> > UPDATE <table>
> > SET ( col1 ,col2 ) = (val1,val2) | ROW (val1[,val2]) | (<query
expression>)
> > WHERE [<search confition>]
> >
> > Example:
> >
> > UPDATE t1
> > SET (col1,col2)=(select sum(col3), sum(col4) from t2 where t2.k=t1.k)
> > WHERE ...
> >
> "select sum(col3), sum(col4) from t2 where t2.k=t1.k" can return
> multiple records! What value should be assigned to col1 and col2 in
> separate record of t1?

It should raise an error saying singleton result required?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Roman Simakov wrote:
>   
> "select sum(col3), sum(col4) from t2 where t2.k=t1.k" can return 
> multiple records! What value should be assigned to col1 and col2 in 
> separate record of t1?

I believe an error (multiple rows in singleton select) should be thrown.


Dmitry

Roman Simakov  wrote:
>>
>> UPDATE t1
>> SET (col1,col2)=(select sum(col3), sum(col4) from t2 where t2.k=t1.k)
>> [WHERE <search confition>]
>>   
> "select sum(col3), sum(col4) from t2 where t2.k=t1.k" can return 
> multiple records! What value should be assigned to col1 and col2 in 
> separate record of t1?
> 
May be, that I missed something, but in my example :

1. Update is performed row by row on t1, which match <search confition>
2. For every row of t1 is executed "select sum(col3), sum(col4) from t2 
where t2.k=t1.k", which returns only one record for one "t1 row"

In general you are right!
UPDATE t1
SET (col1,col2)=(select col3, col4 from t2 where t2.k=t1.k)
[WHERE <search confition>]

This can return more records ... then throw error ... as Martijn and 
Dmitry wrote.

Dmitry Yemanov  wrote:
> LacaK wrote:
>> 1. SELECT ... INTO ... FROM ...
>> SQL:2003 (INTO is before FROM):
>>   SELECT [ <set quantifier> ] <select list>
>>   INTO <select target list>
>>   <table expression>
>>
>>   select f into :v from t;
>>
>> FB (INTO AFTER SELECT, does not allow into before from):
>>
>>   select f from t into :v ;
>>
>> I know, it is only detail, because it does not affect functionality, ... 
>> consider please if it is interesting for you or not
> 
> Tracker is awaiting you :-)
> 
Added

>> 2. UPDATE <multiple column assignment>
>> Are there plans or intention extent UPDATE statement to :
>> UPDATE <table>
>> SET ( col1 ,col2 ) = (val1,val2) | ROW (val1[,val2]) | (<query expression>)
> 
> This is a generic feature (row value constructors) that has nothing to 
> do with UPDATE. Consider also WHERE (fld1, fld2) = (1, 2) etc. Almost 
> everything in the syntax should be extended.
> 
Yes, you are right.
But I guess, that best usability is in update statement.
(When I need update totals in master table using values from detail 
table ... at least update is only statement, where I encounter lack of 
this feature)
I guess, that in select statement you can often rewirite it using for 
example sub-selects.

I do not know, how much of your work is required to implement "row value 
constructor". If it is too dificult task and you think, that it is not 
interstnig feature, then I will not add such request to tracker.
If you think, that it is useful feature I will add ticket ...

-Laco.

LacaK wrote:
>
> If you think, that it is useful feature I will add ticket ...

Please add it.


Dmitry

LacaK escreveu:
> I do not know, how much of your work is required to implement "row value 
> constructor". If it is too dificult task and you think, that it is not 
> interstnig feature, then I will not add such request to tracker.
> If you think, that it is useful feature I will add ticket ...
With current sources, almost everything is not easy...
In a modular architecture, this would be very easy.


Adriano