Oracle delete and insert in one statement
<p>I have a table definition and sample data like below. </p>

<pre><code>user_id,attr_key,attr_val,rating,status
user_1,k1,v1,5,ACT
user_1,k2,v2,6,ACT
user_2,k1,v1,3,INACT
user_2,k2,v2,2,ACT
user_2,k3,v3,8,ACT
root,k1,v1,9,ACT
root,k2,v2,8,ACT
</code></pre>

<p>Is it possible to write ONE query statement which can override data of root user with user_2 and if the status of user_2 data is INACT then it should be copied to root with ACT? Please see expected output below.</p>

<pre><code>Expected output of query:
user_id,attr_key,attr_val,rating,status
user_1,k1,v1,5,ACT
user_1,k2,v2,6,ACT
user_2,k1,v1,3,INACT
user_2,k2,v2,2,ACT
user_2,k3,v3,8,ACT
root,k1,v1,3,ACT
root,k2,v2,2,ACT
root,k3,v3,8,ACT
</code></pre>

<p>Hogan's query: (TODO : delete root before INSERT in same statement)</p>

<pre><code>INSERT INTO some_table
  SELECT 'root', attr_key, attr_val, rating, 'ACT'
  FROM some_table
  WHERE user_id = 'user_1'
</code></pre>

<p>Thanks.</p>