Re: Insert and Update ? (newbie)

There is probably no perfect solution. I'm not sure SQL alone can provide an elegant solution without duplication.
If you don't like any of your solutions create a procedure and do it the old fashion way. i.e. do while rows on table 2; Read table2; if on table 1 update table 1 else insert table1; loop. I sure you get the idea.

Otherwise example 1 is better if you use the () instead of || operator.

e.g.

delete from table1 where (col1,col2) in (select table2.col1,table2.col2 from table2 where table1.col1 = table2.col1 and table1.col2 = table2.col2);

Better chance of using an index.

Example 2. I think your just missing the where clause on the update

e.g.

update table1 set (col3,col4) = (select table2.col3, table2.col4 from

table2 wheretable1.col1 = table2.col1 and table1.col2 = table2.col2)
where (col3,col4) in (select table2.col3, table2.col4 from
table2 wheretable1.col1 = table2.col1 and table1.col2 = table2.col2);

Yes there is duplication.

What would be nice is if you could code:

update table1 set col3 = table2.col3, col4 = table2.col4 where exists (select table2.col3, table2.col4 from table2 wheretable1.col1 = table2.col1 and table1.col2 = table2.col2);

To be able to access the subqueries columns in the main select would be useful. This does rely on the sub-select returning a single row.

There are many ways of coding this statement using in,= exists operators. Or using sub-queries or correlated sub-queries. Most of them probably optimize to the same access path.

Hope this helps....Hopefully someone has better ideas.

Mark wrote:

> Hi all,
>
> this is my first port to this group. There are so many requests for help I
> am surprised that anyone has the time to give any. Thanks in advance to all
> those that do.
>
> Anyway - here is my question.
>
> I have a table that has a primary key constraint and there will probably be
> foreign key constrains to it.
>
> I have another table that I have loaded using sql*loader. I want to merge
> the second table into the first one ie I want to update any matching rows
> in the first table with all the non key values from the second table and I
> want to insert all the rows from the second table into the first table if
> they are not already there.
>
> People must do this so often that there should be a nice ready made way of
> doing it ?
>
> I have tried a few ways and always come to a dead end :
>
> 1. delete all the rows from the first table that exist in the second table
> and then insert all the rows in the second table ? My delete sql looks
> horrible because of multiple columns in the primary key eg.
>
> delete from table1 where col1||col2 = (select table2.col1||table2.col2 from
> table2 where table1.col1 = table2.col1 and table1.col2 = table2.col2);
>
> there must be a better way that that and the delete probably wont work when
> there are foreign key contraints anyway.
>
> 2. update table1 with table2 and then insert all the records from table2
> that are not in table1 ?
>
> update table1 set (col3,col4) = (select table2.col3, table2.col4 from
> table2 wheretable1.col1 = table2.col1 and table1.col2 = table2.col2);
>
> Looks fair enough but when run all the col3s and col4s in rows of table1
> that do not match rows from table2 get set to null !!!!!!
>
> 3. Select all the rows from table1 that do not match rows from table2 into
> a new table3 and then insert all the rows from table2 into table3. Then how
> to get it back into table1 ? I don't think so.
>
> Can someone put me out of my misery ?
>
> thanks,
> Mark.
Received on Thu Feb 04 1999 - 08:42:57 CST