Hi,

with DBISAM, it's possible to do a query like this for update a table :

UPDATE Minutes SET
Libelle = Ouvrages.O_LIBELLE,
Unite = Ouvrages.O_UNITE,
PU = Ouvrages.O_PU,
Total = Q*PU,
Fournis = Ouvrages.Fournis,
Poser = Ouvrages.Poser,
Debourser = Ouvrages.Debourser,
Pose = Ouvrages.Pose,
Fourniture = Ouvrages.Fourniture
FROM Minutes
INNER JOIN Ouvrages
ON (Minutes.MetreA = Ouvrages.O_CODE)
WHERE (Ref = 'A') OR (Ref = 'B')

In ElevateDB this query is not authorized, how can I do to make the same query with ElevateDB

Thanks for your help

Bruno

Josselin

You have to use a subselect in each place you are now using the joined table

>UPDATE Minutes SET
>Libelle = (SELECT O_LIBELLE FROM Ouvrages WHERE Minutes.MetreA = Ouvrages.O_CODE),
....
....
FROM Minutes
WHERE (Ref = 'A') OR (Ref = 'B')

You'll probably have to mess around with the WHERE clause in the subselect to make it product a scalar result.

[Team Elevate]

Thanks Roy, it's working good

I thought I had already tried that....or maybe not... lol

Greetings.

Bruno

Roy,

<< You have to use a subselect in each place you are now using the joined
table >>

Not entirely correct - you can use a row value constructor to do the whole
thing in one shot:

UPDATE Minutes SET (Libelle, Unite, PU, Total, Fournis, Poser, Debourser,
Pose, Fourniture) =
(SELECT
Ouvrages.O_LIBELLE,
Ouvrages.O_UNITE,
Ouvrages.O_PU,
Q*PU,
Ouvrages.Fournis,
Ouvrages.Poser,
Ouvrages.Debourser,
Ouvrages.Pose,
Ouvrages.Fourniture
FROM Ouvrages WHERE (Ouvrages.O_CODE = Minutes.MetreA) AND (Ref = 'A') OR
(Ref = 'B'))

It's kind of weird, but EDB allows a sub-select enclosed within an
UPDATE/DELETE statement to refer to columns in the outer statement, so the
(Q*PU) expression still works fine.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I agree - its kind of weird

Roy Lambert