Board index » delphi » SQL Help: inner join problems with Local SQL

SQL Help: inner join problems with Local SQL

I am trying to execute 2 queries of the type below. And the BDE Local
SQL gives syntax errors, partcularly Invalid Keyword .......

Myy syntax is not up to scratch but can the BDE achieve the former in
particular?

UPDATE TABLE1 SET TABLE1.Field = table2.field where table1.field =
table2

and

UPDATE Suppliers INNER JOIN Products
ON Suppliers.SupplierID = Products.SupplierID SET UnitPrice =
UnitPrice * .95
WHERE CompanyName = 'Tokyo Traders' AND Discontinued = No;

The latter comes from Microsoft Access.

 

Re:SQL Help: inner join problems with Local SQL


Quote
>Myy syntax is not up to scratch but can the BDE achieve the former in
>particular?

>UPDATE TABLE1 SET TABLE1.Field = table2.field where table1.field =
>table2

Local SQL needs to use a subQuery do this

UPDATE TABLE1 SET TABLE1.Field = (Select field from table2 where table1.field =
table2.field)

Quote
>and

>UPDATE Suppliers INNER JOIN Products
>ON Suppliers.SupplierID = Products.SupplierID SET UnitPrice =
>UnitPrice * .95
>WHERE CompanyName = 'Tokyo Traders' AND Discontinued = No;

UPDATE Suppliers  SET UnitPrice = UnitPrice * .95
WHERE CompanyName = 'Tokyo Traders' AND Discontinued = No
and  Suppliers.SupplierID in Select( SupplierID from Products)

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads