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