Re: Nested SQL query involving multiple tables!

On Jan 24, 5:34 pm, "cc" <chris.colclo..._at_nospam.jhuapl.edu> wrote:
> Each table needs its own update statement.
>
> update tab2 t2_alias
> set col_c_2 = null
> where exists
> (select col_a_1
> from
> (select col_a_1, col_b_2, col_C_2, count(col_a_1) over (partition by
> col_a_1
> order by col_a_1) as data_count
> from tab1
> join tab2 on (col_a_1 = col_a_2)
> where col_b_1 = 'true'
> )
> where data_count = 1
> and col_a_1 = t2_alias.col_a_2)

Thanks. I have come up with the following one: UPDATE tab2
SET col_c_2 = null
WHERE col_a_1 IN
 ( SELECT col_a_1 FROM tab1 WHERE col_b_1 = 'true'

	 INTERSECT
	 SELECT col_a_2 FROM tab2 GROUP BY col_a_1 HAVING count(1) = 1

I am not cure about which one is more performant. I was curious to know whether there are any further optimized queries possible. Could anybody comment on this?

In my case, the Tab2 has almost 10 million rows and the Tab1 has 2-3 million rows. In that case, do we have to use any further optimized techniques?

> Perform a similar update for table-1.
Received on Wed Jan 24 2007 - 23:22:27 CST