Hi,

At July 15, 2006, 3:11 PM, emel wrote:

>> Once the subselect returns more then a few thousand
>> rows, all performance goes to hell.
> Only who can write a wrong sql command, will have got hell.

> But I thing, only left outer join needs enabled for this new syntax, and
> wrong subselect forbidden, like now in this examle

> update TableA
> set FieldA = (select FieldB from TableB where TableA.ID = TableB.ID),
> where we can get
> 335544652 error code (multiple rows in singleton select)

The error is appropriate as per the SQL standard. Since, you should
only be trying to assign one value to FieldA, and that the subquery is
returning more than one row.

>> Perhaps instead of adding new
>> update syntax, it would help to increase the performance of subselect
>> queries and it would solve two issues?
>>
> Must some new syntax.

> This is usually used in other db engine:

> update TableA
> set (FieldA1, FieldA2, FieldA3) = (select FieldB1, FieldB2, FieldB3
> from TableB where TableA.ID = TableB.ID)

I'm not sure if the request is in Firebird's issue tracker(I just
could find it, if it is there). And, that syntax is part of the
SQL-2003 standard. But, with your example, you might still get the
same (multiple rows in singleton select) error. If you want to enter
a new entry for it, you can. I don't have an immediate need for that
syntax, but I can see the usefulness of it.

> If we can use new joined update (and delete) this could like it:

> update TableA as A set
> A.FieldA1 = B.FieldB1, A.FieldA2 = B.FieldB2, A.FieldA3 = B.FieldB3
> left outer join TableB as B on ((B.ID = A.ID) and ...)
> where ....

That syntax is not part of the SQL standard. But, I did find in MS-SQL
and PostGreSQL, a FROM clause, as part of the UPDATE statement, that
would permit what you are suggesting. It's not a syntax that I
currently would have a need for. But, you could add it to Firebird's
issue tracker, for further consideration by the developers.

Basically, if it's not in the issue tracker, it would be hard for a
suggestion to really be considered for a future version.

Here's the URL for the issue tracker:
http://tracker.firebirdsql.org/secure/Dashboard.jspa

I would recommend doing a search to see if your suggestions aren't
already in the tracker, before adding them.

--
Best regards,
Daniel Rail
Senior Software Developer
ACCRA Consultants Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)