Dear All,

Could u plz advice me how to write a code to copy records from 2 tables, if the record is there, the record must be updated

I think much more information is needed. What tables are you looking at copying from? And what is meant by if the record is there it must be updated? More details are needed even to post a quick piece of sample code.

Dear Mark,

I'm trying to copy the sales forecast (timrp001) to a customized table (timrp002). I have create a new update session that will insert all the lines from timrp001 to timrp002, but when I changed the Quantity in timrp001, I want this change reflected to timrp002.

I mean, IF NOT INSERT THEN UPDATE, How I can do it by code.

Write the statement for insert in SELECTEMPTY block and for update in SELECTDO block

SELECT timrp002.*
FROM timrp002 for update
WHERE <where condition>
SELECTDO
|***update statements
SELECTEMPTY
|**insert statements
ENDSELECT

But where timrp001? where I will copy timrp001 to timrp002?

I'm trying this, but its not working:


db.retry.point()

select * from timrp001
selectdo
timrp002.item = timrp001.item
timrp002.mnth = lval(sprintf$("%D(%m)", timrp001.fdat))
timrp002.year = lval(sprintf$("%D(%Y)", timrp001.fdat))
timrp002.fqan = timrp001.fqan

IF NOT DB.INSERT(ttimrp002,db.exit.on.dupl) then

SELECT timrp002.*
FROM timrp002 for update

SELECTDO
|***update statements
timrp002.fqan = timrp001.fqan
DB.UPDATE(ttimrp002,DB.RETRY)
COMMIT.TRANSACTION()

ENDSELECT



ENDIF


endselect


COMMIT.TRANSACTION()

message("Update is completed")

Hello,
something like :


select *
from table1
where <your condition>
selectdo
update.table2()
endselect

function update.table2()
{
select *
from table2
where <your condition>
selectdo
db.update(.......)
selectempty
db.insert(.....)
endselect

}


Regards

I do it just like bernd displayed. From looking at your code I think maybe if you just add a where clause to the update it might work.

select tdudi040.*
from tdudi040 for update
where tdudi040._index1 = {:tdpur040.orno}
selectdo
if hold.mast.ordr <> mast.ordr then
if hold.mast.ordr <> 0 then
update.forecast.qty(hold.mast.ordr,-1)
endif
tdudi040.morn = mast.ordr
if mast.ordr <> 0 then
update.forecast.qty(mast.ordr,1)
endif
endif
tdudi040.acko = ack.order
db.update(ttdudi040,db.retry)
selectempty
tdudi040.orno = tdpur040.orno
tdudi040.morn = mast.ordr
tdudi040.acko = ack.order
if mast.ordr <> 0 then
update.forecast.qty(mast.ordr,1)
endif
db.insert(ttdudi040,db.retry)
endselect