This UPDATE clause use to work with DBISAM3:


UPDATE RAM.Name158378 A SET
A.ItemNo     = B.ItemNo,
A.ItemName = B.ItemName_A

FROM RAM.Name158378 LEFT OUTER JOIN TB_Items B
ON B.BranchSysNo = A.BranchSysNo
AND B.YearSysNo   = A.YearSysNo
AND B.ItemSysNo  = A.ItemSysNo

Abdulaziz,

<< This UPDATE clause use to work with DBISAM3: >>

See here:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=19&msg=1020&page=1#msg1020

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<See here:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=19&msg=1020&page=1#msg1020>>

Sorry Tim, but I cannot find a way to use correlated sub-query to update two fields.  In the example you wrote you were updating one field only with
a static value (TRUE).  But in my case I am updating two fields from another table.

Perhaps the OUTER is part of the problem?

I've not yet got stuck into EDB, but I seem to recall encountering this
with DBISAM4.

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Abdulaziz,

<< Sorry Tim, but I cannot find a way to use correlated sub-query to update
two fields.  In the example you wrote you were updating one field only with
a static value (TRUE).  But in my case I am updating two fields from another
table. >>

Could you post the original query ?  I can then give you a modified version
that does what you want.

--
Tim Young
Elevate Software
www.elevatesoft.com

Abdulaziz,

Never mind my last request - I forgot that you posted the query already.
Here's the EDB version:

UPDATE RAM.Name158378 A SET A.ItemNo =
(SELECT B.ItemNo FROM TB_Items B
ON B.BranchSysNo = A.BranchSysNo
AND B.YearSysNo   = A.YearSysNo
AND B.ItemSysNo  = A.ItemSysNo),
A.ItemName =
(SELECT B.ItemName_A FROM TB_Items B
ON B.BranchSysNo = A.BranchSysNo
AND B.YearSysNo   = A.YearSysNo
AND B.ItemSysNo  = A.ItemSysNo)

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<Never mind my last request - I forgot that you posted the query already.
Here's the EDB version:>>

Thanks, I made small change to it to make it work.  Here is the correct one:

UPDATE RAM.Name158378 A SET A.ItemNo =
(SELECT B.ItemNo FROM TB_Items B
WHERE B.BranchSysNo = A.BranchSysNo
AND B.YearSysNo   = A.YearSysNo
AND B.ItemSysNo  = A.ItemSysNo),
A.ItemName =
(SELECT B.ItemName_A FROM TB_Items B
WHERE B.BranchSysNo = A.BranchSysNo
AND B.YearSysNo   = A.YearSysNo
AND B.ItemSysNo  = A.ItemSysNo)