Hi Cao,

Why do you need it done in the same statement? Any queries run within
the same transaction do not see any changes in the table after that
transaction was started, except the ones made in this transaction.

So:

Insert into TableA(Key, ColA,ColB)
Select TableB.Key,TableB.ColA, TableB.ColB from TableB
WHERE not exists (select * from TableA
where TableA.Key = TableB.Key);

Update TableB
set posted = 'T'
where posted <> 'T';

would be a safe query to run.

If it truly bothered you, you could put the combination into a stored
procedure, however I find your first query odd. If you have a field
in tableB to say whether it has been posted or not, why do you rely
on the not exists? Wouldn't the following be more efficient?

Insert into TableA(Key, ColA,ColB)
Select TableB.Key,TableB.ColA, TableB.ColB from TableB
where posted <> 'T';

Update TableB
set posted = 'T'
where posted <> 'T';

Or as a stored procedure, you could run:

for select TableB.Key,TableB.ColA, TableB.ColB from TableB
where posted <> 'T'
into :tablebkey, :tablebcola, :tablebcolb
do
begin
Insert into TableA(Key, ColA,ColB) values
(:tablebkey, :tablebcola, :tablebcolb);

Update tableb
set posted = 'T'
where key = :tablebkey;
end

Unless you really want to reduce it to a single operation, I wouldn't
bother creating the stored procedure though.

Adam



--- In firebird-support@yahoogroups.com, "Cao Ancoinc" <groups@a...>
wrote:

> Hi everyone
>
> I would like to insert records from table B into Table A where
> the record does not exist in Table A and mark the record in
> table B as posted
>
> eg
> Table A Table B
> Key ColA ColB ColC Key ColA ColB Posted

> === === ==== ==== === === ==== ======
> A 1 11 111 B 2B 22B
> B 2 22 222 D 4D 44D
> C 3 33 333 E 5E 55E
>
>
>
> Result
> Table A Table B
> Key ColA ColB ColC Key ColA ColB Posted

> === === ==== ==== === === ==== ======
> A 1 11 111 B 2B 22B
> B 2 22 222 D 4D 44D True
> C 3 33 333 E 5E 55E True
> D 4D 44D
> E 5E 55E
>
>
> I have used the following sql
> Insert into TableA(Key, ColA,ColB)
> Select TableB.Key,TableB.ColA, TableB.ColB from TableB
> WHERE not exists (select * from TableA
> where TableA.Key = TableB.Key);
>
> How do I mark the record as posted in TableB using the same sql
>
> I would appreciate any help from the gurus out there
>
> Regards Cao