Board index » delphi » SQL Statement

Conrad Smith

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

SQL Statement

Hi there

I'm puzzling with two SQL statements that i need to apply, and i cant
seem to get it right.

The first problem is the following.

I've got two tables TableA and TableB
TableA consists of fields Aa,Ab and Ac
TableB consists of fields Ba,Bb and Bc

i want to update field Ac with the Value of Bc
if Bb is equal to Ab
what i had in mind was

UPDATE TableA,TableB
set TableA.Ab=TableB.Bb
where TableA.Ac=TableB.Bc

but the compiler does not except this
What is the correct format for the SQL statement.

The second problem is the following.

Three tables TA,TB,TC
each having the same field F1, and various others
now, i want to have a list of all the records in TA
that does not exist in the combination of TB and TC

what i've tried was

select * from TA
where
TA.F1 not in
(select F1 from TB
UNION
select F1 from TC)

but the UNION statement does not seem to work after a not in

if used seperately, it works, ie
select F1 from TB
UNION
select F1 from TC
combines the two table records
and
select * from TA
where
TA.F1 not in
(select F1 from TB)
return the list of record in TA that is not in TB

But how do i combine these

Thanks
Conrad Smith

(replace %DOT% with . in my return address)

 
Glatzfam

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Statement


Quote

>I'm puzzling with two SQL statements that i need to apply, and i cant
>seem to get it right.

>The first problem is the following.

>I've got two tables TableA and TableB
>TableA consists of fields Aa,Ab and Ac
>TableB consists of fields Ba,Bb and Bc

>i want to update field Ac with the Value of Bc
>if Bb is equal to Ab
>what i had in mind was

>UPDATE TableA,TableB
>set TableA.Ab=TableB.Bb
>where TableA.Ac=TableB.Bc

I'm kinda rusty with my SQL but you could try something like this:

UPDATE TableA
set Ab =
 (select tableb.Bb from TableB, tableA
where
TableA.Ac = TableB.Bc)

- Show quoted text -

Quote
>Three tables TA,TB,TC
>each having the same field F1, and various others
>now, i want to have a list of all the records in TA
>that does not exist in the combination of TB and TC

>what i've tried was

>select * from TA
>where
>TA.F1 not in
>(select F1 from TB
>UNION
>select F1 from TC)

>but the UNION statement does not seem to work after a not in

>if used seperately, it works, ie
>select F1 from TB
>UNION
>select F1 from TC
>combines the two table records
>and
>select * from TA
>where
>TA.F1 not in
>(select F1 from TB)
>return the list of record in TA that is not in TB

>But how do i combine these

Select F1 from TC
where F1 Not IN (Select F1 From TB) and
F1 Not IN (Select F1 From TC)

********************************
Michael Glatz
glatzfa...@aol.com
mgl...@briefcase.com

BlueG

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Statement


You can use a From clause:

update TableA set
Ab = TableB.Bb
from TableA, TableB
where TableA.Ac=TableB.Bc

Hope this helps.

Quote
Conrad Smith wrote in message <369B18DC.A19E0...@icon.co.za>...
>Hi there

>I'm puzzling with two SQL statements that i need to apply, and i cant
>seem to get it right.

>The first problem is the following.

>I've got two tables TableA and TableB
>TableA consists of fields Aa,Ab and Ac
>TableB consists of fields Ba,Bb and Bc

>i want to update field Ac with the Value of Bc
>if Bb is equal to Ab
>what i had in mind was

>UPDATE TableA,TableB
>set TableA.Ab=TableB.Bb
>where TableA.Ac=TableB.Bc

>but the compiler does not except this
>What is the correct format for the SQL statement.

>The second problem is the following.

>Three tables TA,TB,TC
>each having the same field F1, and various others
>now, i want to have a list of all the records in TA
>that does not exist in the combination of TB and TC

>what i've tried was

>select * from TA
>where
>TA.F1 not in
>(select F1 from TB
>UNION
>select F1 from TC)

>but the UNION statement does not seem to work after a not in

>if used seperately, it works, ie
>select F1 from TB
>UNION
>select F1 from TC
>combines the two table records
>and
>select * from TA
>where
>TA.F1 not in
>(select F1 from TB)
>return the list of record in TA that is not in TB

>But how do i combine these

>Thanks
>Conrad Smith

>(replace %DOT% with . in my return address)

Marco Roc

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Statement


Hi,

On Tue, 12 Jan 1999 11:41:48 +0200, Conrad Smith

Quote
<conrad%DOT%q...@icon.co.za> wrote:
>Hi there

>I'm puzzling with two SQL statements that i need to apply, and i cant
>seem to get it right.

>The first problem is the following.

>I've got two tables TableA and TableB
>TableA consists of fields Aa,Ab and Ac
>TableB consists of fields Ba,Bb and Bc

>i want to update field Ac with the Value of Bc
>if Bb is equal to Ab
>what i had in mind was

>UPDATE TableA,TableB
>set TableA.Ab=TableB.Bb
>where TableA.Ac=TableB.Bc

>but the compiler does not except this
>What is the correct format for the SQL statement.

What does the compiler have to do with the SQL statement?

Anyway, the UPDATE doesn't support a list of tables to update (even
though I see you only really want to write on one).  You need
subselects.  Something like this should work:

UPDATE TableA
SET Ab=(SELECT Bb FROM TableB WHERE TableA.Ac=TableB.Bc)
WHERE Ac = ANY (SELECT Bc FROM TableB)

You must consider, though, that the ANY clause and subselects are not
supported by all implementations of SQL.  If it doesn't work try to
substitute the ENTIRE WHERE clause with one of these:

 WHERE EXISTS (SELECT Bc FROM TableB WHERE TableA.Ac=TableB.Bc)
   or
 WHERE Ac=(SELECT Bc FROM TableB WHERE TableA.Ac=TableB.Bc)

- Show quoted text -

Quote
>The second problem is the following.

>Three tables TA,TB,TC
>each having the same field F1, and various others
>now, i want to have a list of all the records in TA
>that does not exist in the combination of TB and TC

>what i've tried was

>select * from TA
>where
>TA.F1 not in
>(select F1 from TB
>UNION
>select F1 from TC)

>but the UNION statement does not seem to work after a not in

>if used seperately, it works, ie
>select F1 from TB
>UNION
>select F1 from TC
>combines the two table records
>and
>select * from TA
>where
>TA.F1 not in
>(select F1 from TB)
>return the list of record in TA that is not in TB

I would have thought it should work like you wrote it.  Maybe it is
some limit in the implementation of SQL you are using.

Work around the problem eliminating the UNION:

  SELECT * FROM TA WHERE
     (F1 NOT IN SELECT F1 FROM TB) AND
     (F1 NOT IN SELECT F1 FROM TC)

    or

  SELECT * FROM TA WHERE
     (NOT EXISTS SELECT F1 FROM TB WHERE TA.F1=TB.F1) AND
     (NOT EXISTS SELECT F1 FROM TC WHERE TA.F1=TC.F1)

Regards,

--
Marco Rocci
MicroEra srl
Turin, Italy

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Statement


Oleg Roshchi

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Statement


Just addition about performance:

The second example below seems to be much more effective
especially if there are indexes in TB and TC on field F1,
because here it is index search in the subqueries,
in the first example - full scan!

Oleg

Quote
Marco Rocci wrote in message <369b2eed.9772...@tor-nn1-ca.netcom.ca>...
>Hi,

>On Tue, 12 Jan 1999 11:41:48 +0200, Conrad Smith
><conrad%DOT%q...@icon.co.za> wrote:

..............................
..............................
Quote
>Work around the problem eliminating the UNION:

>  SELECT * FROM TA WHERE
>     (F1 NOT IN SELECT F1 FROM TB) AND
>     (F1 NOT IN SELECT F1 FROM TC)

>    or

>  SELECT * FROM TA WHERE
>     (NOT EXISTS SELECT F1 FROM TB WHERE TA.F1=TB.F1) AND
>     (NOT EXISTS SELECT F1 FROM TC WHERE TA.F1=TC.F1)

>Regards,

>--
>Marco Rocci
>MicroEra srl
>Turin, Italy

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Statement


Liboni Stefan

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:SQL Statement


Quote
On Tue, 12 Jan 1999, Conrad Smith wrote:
> Hi there

> I'm puzzling with two SQL statements that i need to apply, and i cant
> seem to get it right.

> The first problem is the following.

> I've got two tables TableA and TableB
> TableA consists of fields Aa,Ab and Ac
> TableB consists of fields Ba,Bb and Bc

> i want to update field Ac with the Value of Bc
> if Bb is equal to Ab
> what i had in mind was

> UPDATE TableA,TableB
> set TableA.Ab=TableB.Bb
> where TableA.Ac=TableB.Bc

> but the compiler does not except this
> What is the correct format for the SQL statement.

> The second problem is the following.

> Three tables TA,TB,TC
> each having the same field F1, and various others
> now, i want to have a list of all the records in TA
> that does not exist in the combination of TB and TC

> what i've tried was

> select * from TA
> where
> TA.F1 not in
> (select F1 from TB
> UNION
> select F1 from TC)

> but the UNION statement does not seem to work after a not in

Hi,
regarding the second statement, it doesn't want to work because the UNION
is not allowed to be in a subquery.
Try to divide the where condition:

SELECT *
FORM TA
WHERE TA.F1 not IN (SELECT F1 FROM TB)
      and
      TA.F1 not IN (SELECT F1 FROM TC)

Hope it will work!

Other Threads

1. Please Help (This message Pertains to SQL and the Create Table SQL Statement)

2. SQL Statement fromo ACCESS to DELPHI

3. Problem with sql statement and query component

4. SQL Statement fromo ACCESS to DELPHI

5. Problem with DateTime through SQL Statement

6. KaDao SQL Statement error

7. SQL STATEMENT

8. SQL Statement month grouping

9. Help with SQL Statement applied to Paradox tables

10. SQL statements in object inspector