Hi All,

Probably a easy one for the experts - i hope
I need to insert values to a table from another table (TableB), but
also a dynamic paramater are used. But only non-existing values should
be inserted. This means first time it's run, all values from TableB
should be inserted in place of FieldC in TableA.

I have:

Table A
=======================
FieldA Generated
FieldB Integer
FieldC Integer

Table B
=======================
FieldC Integer

Field B should be a dynamic parameter, set on runtime.
Field C should be selected from another table B.

Following data wanted:

TableA Sample Data
========================
1, 1, 1
2, 1, 2
3, 1, 3
4, 1, 4

etc..

I've tried following:  

insert into tablea
(fieldb, fieldc)
values(1, select fieldc from tableb);

but it fails with:
ElevateDB Error #1011 An error occurred with the query SELECT ALL
"FieldC" AS "FieldC" FROM "TableB" (A scalar query can only return a
single value).

I guess it means it returns many rows instead of 1 needed for the
insert, but how can this be done?

Thanks in advance

Regards,
Hüseyin A.

Hi

The statement below should work.

insert into tablea
(fieldb, fieldc)
select 1, fieldc from tableb;

If you use VALUES then the SELECT must return one value.  For example:

insert into tablea
(fieldb, fieldc)
values(1, select fieldc from tableb WHERE ID = 1);

Richard Harding

<<But only non-existing values should be inserted. This means first time it's run, all values from TableB
should be inserted in place of FieldC in TableA.  >>

I read your post again.  The query below is probably closer to what you require.

INSERT INTO TableA
 (FieldB, FieldC)
 SELECT 1, FieldC FROM TableB
   WHERE
     FieldC NOT IN (SELECT A.FieldC FROM TableA A)

Richard Harding

Richard,

Works great! Thank you

Regards,
Hüseyin A.

Richard Harding wrote:

> <<But only non-existing values should be inserted. This means first
> time it's run, all values from TableB should be inserted in place of
> FieldC in TableA.  >>
>
> I read your post again.  The query below is probably closer to what
> you require.
>
> INSERT INTO TableA
>   (FieldB, FieldC)
>   SELECT 1, FieldC FROM TableB
>     WHERE
>       FieldC NOT IN (SELECT A.FieldC FROM TableA A)
>
> Richard Harding