RE: SQL insert - all fields

["Jonathan Mason" <jonathan.mason@xxxxxxxxxxxxxxxx>]

Hi Jeff

I haven't tested it, but the following should insert the whole of the
original record into the "old" item master:

INSERT INTO OLITMMST (SELECT * FROM DMITMMST WHERE ACREC = 'D')

The above won't put the deletion date in, and looking at the manual I
wouldn't expect the following to work, but do try it:

INSERT INTO OLITMMST (SELECT * FROM DMITMMST WHERE ACREC = 'D'), CURRENT
DATE VALUES

You might have to do it in two stages, and run:

INSERT INTO OLITMMST (SELECT * FROM DMITMMST WHERE ACREC = 'D')

UPDATE OLITMMST A
   SET A.DLDATE = CURRENT DATE 
 WHERE (SELECT ACREC FROM DMITMMST B WHERE A.item = B.item) = 'D'
   AND A.DLDATE = 0

Hope it works

All the best

Jonathan
www.astradyne-uk.com