title: Oracle SQL & scriptella: How to do conditional insertion or updation based on select criterion?
body: <p>I am facing one problem regarding Oracle Sql queries in Scriptella.</p>
  
  <p>My intention is - I have to fetch some column data from PRODUCT_PRICE table for each row (determined using where clause) fetched from PRODUCT table and then if the fetched data from PRODUCT_PRICE contains nothing or null, then I have to insert a new column for the price value corresponding to product &amp; store id into the table or else if there exists a row corresponding to product &amp; store id, then I have to update the price.</p>
  
  <p>The following code should clear the logic which I am describing -</p>
  
  <pre><code>&lt;query connection-id="db"&gt;
  select PRODUCT_ID as "product_id1", STORE_ID as "store_id1" from PRODUCT
    &lt;query connection-id="db"&gt;
    select REGULAR_PRICE, PRODUCT_ID as "product_id2", STORE_ID as "store_id2" from PRODUCT_PRICE where PRODUCT_ID=?product_id1 and STORE_ID=?store_id1
      &lt;script connection-id="db" if="rownum==0"&gt;
      insert into PRODUCT_PRICE(REGULAR_PRICE, PRODUCT_ID, STORE_ID) values(?price, product_id1, store_id1)
      &lt;/script&gt;
      &lt;script connection-id="db" if="rownum gt 0"&gt;
      update REGULAR_PRICE=?price where PRODUCT_ID=?product_id2 and STORE_ID=?store_id2
      &lt;/script&gt;
    &lt;/query&gt;
  &lt;query&gt;
  </code></pre>
  
  <p>My question is - this is not working! As you can see I have to insert product_id, store_id as well as 'price' into the PRODUCT_PRICE table if there is no data present corresponding to the product &amp; store id. Also I have to update only price if there exists a row of same product &amp; store_id. But when the resulset fetched in the second query contains nothing, then the script with condition "rownum==0" is not working. SO how can achieve this in Oracle?</p>
  
  <p>In MySQL or SyBase database, I know there is one syntax called "if exists(select ....) / then something / else something"  but I could not find anything in the Oracle database. How to achieve this conditional insertion or updation based on select criterion in Oracle database because our project is based on Oracle?? </p>
  
  <p>P.S. - Here "price" variable is already found before the first query. I have put only the relevant part of the code.</p>