Append records from other table

let's say, I have 2 tables with exactlly same structure.
table1 is the main table which has 100 records in it.
table2 is holding 5 records.
is it possible to write a SQL to perform this function :

append table2 to table1
if the reocord alread exist in table1 ,update it ( prevent duplicated records in
table1 )

Roland Wingerter Wrote:

> "eric wu" wrote
> > let's say, I have 2 tables with exactlly same structure.
> > table1 is the main table which has 100 records in it.
> > table2 is holding 5 records.
> > is it possible to write a SQL to perform this function :
> >
> > append table2 to table1
> > if the reocord alread exist in table1 ,update it ( prevent duplicated

> > records in table1 )
> --------
> OLH UpdateSet Class, method appendUpdate().
> 
> Roland
> 
> 
sorry, I mean the SQL statement , like 

sql = "update collection set worth = 900 where item = 'STRING'"

"eric wu" wrote
> let's say, I have 2 tables with exactlly same structure.
> table1 is the main table which has 100 records in it.
> table2 is holding 5 records.
> is it possible to write a SQL to perform this function :
>
> append table2 to table1
> if the reocord alread exist in table1 ,update it ( prevent duplicated 
> records in table1 )
--------
OLH UpdateSet Class, method appendUpdate().

Roland

thank you
but   I have many columns in the table

SET t1.amount = t1.amount + ;   --->     only work on a column
"amount"

INSERT INTO Table1 (CustID, Amount) ;
---> only work on 2 colmuns CustID and  Amount
do I have to write all column  names in the statement ?


"Roland Wingerter" <ich@daheim.de> wrote in message 
news:oMkiv0XfIHA.1880@news-server...
> "eric wu" wrote > Roland Wingerter Wrote:
>>
>>> "eric wu" wrote
>>> > let's say, I have 2 tables with exactlly same structure.
>>> > table1 is the main table which has 100 records in it.
>>> > table2 is holding 5 records.
>>> > is it possible to write a SQL to perform this function :
>>> >
>>> > append table2 to table1
>>> > if the reocord alread exist in table1 ,update it ( prevent
duplicated 
>>> > records in table1 )
>>> --------
>>> OLH UpdateSet Class, method appendUpdate().
>>>
>> sorry, I mean the SQL statement , like sql = "update collection
set worth 
>> = 900 where item = 'STRING'"
> -----------
> You would have to do it in two steps. Here is a sample.
>
> Suppose your tables have the fields CustID, Amount.
> // 1. Update existing rows in Table1
> UPDATE Table1 T1 ;
> SET t1.amount = t1.amount + ;
> (SELECT t2.amount FROM Table2 t2 WHERE t1.custID = t2.custID)
>
> // 2. Append new rows to Table1
> INSERT INTO Table1 (CustID, Amount) ;
> SELECT CustID, Amount ;
> FROM Table2 ;
> WHERE CustID NOT IN (SELECT CustID FROM Table1)
>
> Hope this helps
>
> Roalnd
>
>
> 

"eric wu" wrote > Roland Wingerter Wrote:
> 
>> "eric wu" wrote
>> > let's say, I have 2 tables with exactlly same structure.
>> > table1 is the main table which has 100 records in it.
>> > table2 is holding 5 records.
>> > is it possible to write a SQL to perform this function :
>> >
>> > append table2 to table1
>> > if the reocord alread exist in table1 ,update it ( prevent
duplicated 
>> > records in table1 )
>> --------
>> OLH UpdateSet Class, method appendUpdate().
>> 
> sorry, I mean the SQL statement , like 
> 
> sql = "update collection set worth = 900 where item = 'STRING'"
-----------
You would have to do it in two steps. Here is a sample.

Suppose your tables have the fields CustID, Amount. 

// 1. Update existing rows in Table1
UPDATE Table1 T1 ;
SET t1.amount = t1.amount + ;
(SELECT t2.amount FROM Table2 t2 WHERE t1.custID = t2.custID)

// 2. Append new rows to Table1
INSERT INTO Table1 (CustID, Amount) ;
SELECT CustID, Amount ;
FROM Table2 ;
WHERE CustID NOT IN (SELECT CustID FROM Table1)

Hope this helps

Roalnd