 |
 |
Archives of the TeradataForum
Message Posted: Sun, 17 Oct 2004 @ 11:56:01 GMT
| Subj: | | Re: Can this query be written more efficient |
| |
| From: | | Dieter Noeth |
Anomy Anom wrote:
| | Basically trying to insert into a copy tables all rows from table (except the ones that exist in mtable) + insert into table copy all the
rows from mtable. | |
INSERT INTO table_copy
SELECT *
FROM table
WHERE (X,Y) NOT IN (SELECT DISTINCT X,Y
FROM Mtable)
;insert into table_copy
select *
from Mtable;
| | Can this query be written in a more efficient manner. It currently takes 4 hours with table, 61+ millions rows and mtable, 20+
thousands rows. | |
INSERT INTO table_copy
select
t.x,
t.y,
/** if there's no matching row, then m.newcol is NULL and replaced by
t.newcol **/
coalesce(m.newcol, t.newcol)
from
table t left join mtable m
on t.x = m.x
and t.y = m.y
coalesce(m.newcol, t.newcol): .
Dieter
| |
