Hi,

(Delphi 5 Pro)

I'm trying to get an update query to run against a paradox table that
includes a from clause.  The query.

update sales
set cashCreditRate = d1.rate
FROM    sales d, payrates d1
where d.cash_or_credit = d1.id

(tried both with aliases d & d1 and table names)

The query fails with a complaint about the "from".
The DBD help states:
"There are no restrictions on or extensions to the ANSI-standard UPDATE
statement."

I imported the tables into MS SQL7 to test the SQL statements.  Runs fine
with SQL7.

I have come to the conclusion that the help statement is wrong and that
there are restrictions to the update statements the DBE can execute against
Paradox tables.

Does anyone know the answer, is it a BDE restriction?  Or is it an extension
supported by SQL7 that I got used to using without realizing it was an
extension?

Thanks
Bill

This is because you need to use a sub-select.  Try changing it to this:

 update sales
 set cashCreditRate = (Select d1.rate
 FROM    sales d, payrates d1
 where d.cash_or_credit = d1.id)

I'm not sure if sub-selects will work with Paradox - I know they work with
client/server databases.  If it doesn't work, you'll have to do the select
as a separate query and then update the records individually.

-Dell

Dell,

It does not work either.  It got past the SQL parsing but failed because it
returned more than one row.

Thanks.
Bill

news:3cc821c9_1@dnews...

You neglect to say how the tables are joined.  Which record in
"payrates" is Paradox supposed to use?

----------------------------------------------------------------
Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259
mailto:i...@sundialservices.com  (PGP public key available.)

The join is there, using the aliases.  This is the query using table names.

UPDATE sales
SET cashCreditRate = payrates.rate
FROM sales, payrates
WHERE sales.cash_or_credit = payrates.id

It runs correctly using MS SQL7 with names or aliases both directly and
using ODBC through Dephi.
It does not work through the BDE with Delphi, Paradox or DBD.
It also does not work using MS Access, access tables.

I can get the same results using QBE.
Query
payrates.db | Id        | Rate     |
                   | _join1 | _theRate |

sales.DB | Cash_or_Credit | Cashcreditrate     |
               | _join1               | change to _theRate |
EndQuery

But the QBE cannot be converted to SQL statements by either the DBD or
Paradox 9.

I learned the format from the MS SQL7 help files some time ago.  I'm leaning
more and more to it being an extension to SQL in MS SQL7.

Thanks
Bill

news:3CC83A5A.49EA@sundialservices.com...

Are you sure that FROM in an Update query is in the SQL 92 standard.

Local SQL does not support From in Update.  You will have to rewrite your update
to use a SubQuery like this

update sales D
set cashCreditRate =
(Select d1.rate
FROM    Payrates d1
where d.cash_or_credit = d1.id)
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Brian,

I do not know if meets SQL 92 (thinking it does not).

Thanks for the suggestion, it works.

Bill
"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:0fchcucb14sjfcrvuda5vgtfj16cbnj28r@4ax.com...