Board index » delphi » update query with "From" clause

update query with "From" clause

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

 

Re:update query with "From" clause


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

Re:update query with "From" clause


Dell,

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

Thanks.
Bill

Quote
"Dell Stinnett" <dell.stinn...@zcsterling.com> wrote in message

news:3cc821c9_1@dnews...
Quote
> 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

Re:update query with "From" clause


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

Quote
Bill Hannan wrote:

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

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

- Show quoted text -

Quote
> Fast(!), automatic table-repair with two clicks of the mouse!
> ChimneySweep(R):  Release 4.0 is here!!
> http://www.sundialservices.com/products/chimneysweep

Re:update query with "From" clause


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

Quote
"Sundial Services" <info_...@sundialservices.com> wrote in message

news:3CC83A5A.49EA@sundialservices.com...
Quote
> You neglect to say how the tables are joined.  Which record in
> "payrates" is Paradox supposed to use?

> Bill Hannan wrote:

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

> ----------------------------------------------------------------
> Sundial Services :: Scottsdale, AZ (USA) :: (480) 946-8259
> mailto:i...@sundialservices.com  (PGP public key available.)
> > Fast(!), automatic table-repair with two clicks of the mouse!
> > ChimneySweep(R):  Release 4.0 is here!!
> > http://www.sundialservices.com/products/chimneysweep

Re:update query with "From" clause


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

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

Re:update query with "From" clause


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

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

Other Threads