dbaccess; insert in table2 depending to content of table1
Hallo,
I have 2 Tables
Table1 (our Articles with >30000 Records)
ArtNr
ABC
...
Table2 (This Table is emty)
ArtNr
MGK1
MGK2
MGK3
...
I must fill Table2 for all Articles with the dollowing conditions
ABC=A => MGK1=1
ABC=B => MGK1=2
ABC=C => MGK1=3
How do I have to create my sql Script in dbaccess?
If I make an insert, what must I do with the other fields of Table2,
mus I fill them with 0, so that they are initialices?
Greetings
Ralf
...
Ralf Hackmann wrote:
> Hallo,
>
> I have 2 Tables
>
> Table1 (our Articles with >30000 Records)
>
> ArtNr
> ABC
> ...
>
> Table2 (This Table is emty)
> ArtNr
> MGK1
> MGK2
> MGK3
> ...
>
> I must fill Table2 for all Articles with the dollowing conditions
> ABC=A => MGK1=1
> ABC=B => MGK1=2
> ABC=C => MGK1=3
>
> How do I have to create my sql Script in dbaccess?
> If I make an insert, what must I do with the other fields of Table2,
> mus I fill them with 0, so that they are initialices?
INSERT INTO table2(artnr, mgk1)
SELECT artnr, 1
FROM table1
WHERE abc="A"
and then repeat this for the other values of ABC and MGK1. Note that
table and column names are case-insensitive.
This assumes that
(a) the other columns of table2 allow nulls and
(b) the software that reads table2 doesn't expect non-null values.
You need to find out for yourself whether these assumptions are true.
If need to set the other columns then the SQL becomes:
INSERT INTO table2(artnr, mgk1, mgk2, mgk3)
SELECT artnr, 1, 0, 0
FROM table1
WHERE abc="A"
--
Ian
The Hotmail address is my spam-bin. Real mail address is iang
at austonley org uk
Ralf,
insert into table2(artnr, mgk1)
select artnr, case abc when 'A' then 1 when 'B' then 2 when 'C' then 3 end
from table1;
If the remaining columns in table2 accept NULLs or have DEFAULT clauses in
their definitions, then this will work, otherwise you will have to provide
some reasonable value for those columns to the INSERT in the SELECT clause.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Fri, Dec 10, 2010 at 3:00 AM, Ralf Hackmann <ralf.hackmann@gmail.com>wrote:
> Hallo,
>
> I have 2 Tables
>
> Table1 (our Articles with >30000 Records)
>
> ArtNr
> ABC
> ...
>
> Table2 (This Table is emty)
> ArtNr
> MGK1
> MGK2
> MGK3
> ...
>
> I must fill Table2 for all Articles with the dollowing conditions
> ABC=A => MGK1=1
> ABC=B => MGK1=2
> ABC=C => MGK1=3
>
> How do I have to create my sql Script in dbaccess?
> If I make an insert, what must I do with the other fields of Table2,
> mus I fill them with 0, so that they are initialices?
>
> Greetings
>
> Ralf
>
> ...
>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>