Re: Nested SQL query involving multiple tables!

From: cc <chris.colclough_at_nospam.jhuapl.edu>
Date: Wed, 24 Jan 2007 07:34:10 -0500
Message-ID: <ep7jo4$o2p$1@aplcore.jhuapl.edu>

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)

Perform a similar update for table-1.

<qazmlp1209_at_rediffmail.com> wrote in message news:1169631874.023681.125770_at_v45g2000cwv.googlegroups.com...

> On Jan 23, 9:07 pm, "cc" <chris.colclo..._at_nospam.jhuapl.edu> wrote:


>>> For the above sample records, only the following record

>>> 121         15          Val1

>>> should become

>>> 121         15          null

>>
>> select col_a_1, col_b_2, null
>> 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

> This shows the row status without actually performing the update. I
> just thought of using something similar to "insert into tab2 values",
> but it does not seem to help.
> How exactly I should adapt this query to update the Tab2?
> Also, for the matched records, the col_b_1 value should be changed to
> 'false' from 'true', in the Tab1.
>
> Can all these be done in a single SQL query?
> 

Received on Wed Jan 24 2007 - 06:34:10 CST