> 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)

> 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 can redeem it with a
update ... FieldA1 = (select FieldB1...), FieldA2 = (select
FieldB2...), FieldA3 = (select FieldB3...) ...
command, but with complicated where it is horror.

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 ....

This new syntax inportant in a more complex instance like this:

update TableA as A set
A.FieldA1 = B.FieldB1, A.FieldA2 = B.FieldB2 + C.FieldC2, A.FieldA3 =
B.FieldB3 + C.FieldC3 + D.FieldD3
left outer join TableB as B on ((B.ID = A.ID) and ...)
left outer join TableC as C on ((C.ID = A.ID) and ...)
left outer join TableD as D on ((D.ID = A.ID) and ...)
where ....

One recent argument:
I like if I can firstly execute a select statement and when result
assure me then execute update.

For previous exemple, before execute this update I can control it with
next select:

update TableA as A set
A.FieldA1 = B.FieldB1, A.FieldA2 = B.FieldB2 + C.FieldC2, A.FieldA3 =
B.FieldB3 + C.FieldC3 + D.FieldD3
left outer join TableB as B on ((B.ID = A.ID) and ...)
left outer join TableC as C on ((C.ID = A.ID) and ...)
left outer join TableD as D on ((D.ID = A.ID) and ...)
where ....

select A.FieldA1 as Old1, B.FieldB1 as New1,
A.FieldA2 as Old2, (B.FieldB2 + C.FieldC2) as New2,
A.FieldA3 as Old3, (B.FieldB3 + C.FieldC3 + D.FieldD3) as New3
from TableA as A
left outer join TableB as B on ((B.ID = A.ID) and ...)
left outer join TableC as C on ((C.ID = A.ID) and ...)
left outer join TableD as D on ((D.ID = A.ID) and ...)
where ....

If control select (maybe very complex and long!) is comforting, I can
rewrite it to update command easy and with less failure .

eMeL

(Sorry for my terrible english)