At 01:19 PM 13/08/2003 +0200, you wrote:

> >"INSERT INTO RECON (field1, field2, field3)
> >
> >SELECT fieldA, fieldB, SUM(FieldC)
> >FROM Table2
> >WHERE fieldA = 'xyz'
> >GROUP BY fieldA, fieldB
> >
> >WHERE Table2.BATCHNO = RECON.BATCHNO"
> >
> >??? Anyone got any idea of how I can do this?
>
>Sorry - I meant
>
>"UPDATE RECON
>SET
>field1 = (SELECT BATCHNO, SUM(fieldA) FROM Table2
> GROUP BY BATCHNO) <--- "this does not compute"
>WHERE
>RECON.BATCHNO = Table2.BATCHNO"

a) you can't set a single column to be multiple values
b) you can't get multiple values in a subquery anyway

Use a stored procedure, setting up a loop that reads the output from the
two separate queries over twice-aliased Table2, into variables, and finally
inserts once into the RECON table per loop. That is to say, don't try to
both insert a record and update it.

Unfortunately, the first query uses fake identifiers, so it's impossible to
tell how you are getting BATCHNO into the RECON table. Thus I can't give
you any meaningful sample.

Wouldn't it would reduce confusion to start the whole question over?

heLen