Hello both Tom and Walter,

That's an interesting issue and I join Tom in his quest for knowledge in
this matter. I'd push it even further, asking whether limiting the
sub-select to contain just a single record (one or none) would be more
efficient, or is the engine doing that anyway for EXISTS clauses?
My modification of Tom's suggested improvement:

UPDATE TableA TA
SET TA.USED = 'T'
WHERE EXISTS (
SELECT FIRST(1) 1 FROM TableB TB WHERE TA.LINE = TB.LINE
)

Maybe we could split the work among us and perform some tests on large
tables?

regards
Tomasz

p.s. Some time ago, one of my co-workers did some research and came up
with the revelation that EXISTS in FB are in fact implemented via JOINs.
I've never verified that, though, having no particular reason to make
the effort.
T.

W dniu 2011-10-06 11:43, tomc7777777 pisze:

>
>
> --- In firebird-support@yahoogroups.com, Svein Erling Tysvær<svein.erling.tysvaer@...> wrote:
>>
>>> I have a table TableA with the following data:
>>>
>>> LINE USED
>>> 1 F
>>> 2 F
>>> 3 F
>>> 95 F
>>> 96 F
>>>
>>> and a table TableB with the following data:
>>>
>>> LINE
>>> 1
>>> 2
>>> 3
>>> 4
>>>
>>> and I need to put a 'T' on the USED column when the line's number is the
>>> same in both tables (in this case, when it is 1, 2 or 3). Of course, there
>>> are much more numbers and I dont know them.
>>>
>>> How I can make an update on TableA when it has the same numbers that TableB
>>> has?
>>
>> Hi Walter,
>>
>> UPDATE TableA TA
>> SET TA.USED = 'T'
>> WHERE EXISTS(SELECT * FROM TableB TB
>> WHERE TA.LINE = TB.LINE)
>>
>> Set
>>
>
> Hi Set,
>
> Out of interest, does SELECT 1 work identically and if so whether it performs any quicker (if this were a very large table) than SELECT * in the sub-select?
>
> In other words, I'm unclear how the rows from the sub-select are 'materialised' prior to joining to TableA and whether * is needed return TB.LINE so as to test for TA.LINE = TB.LINE or not (if that makes sense!).
>
> e.g.
> UPDATE TableA TA
> SET TA.USED = 'T'
> WHERE EXISTS(SELECT 1 FROM TableB TB WHERE TA.LINE = TB.LINE)
>
> Thanks,
> Tom
>
>

--
__--==============================--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==============================--__