title: Query to get number that is smaller than the fixed number
body: <p>I'm given a task to find out which have the smaller or equal MOQ(Minimum order quantity) compare to the fixed MOQ. At first, I used the method which was while read() (using c#) and go for line by line.</p>
  <p>But it is slow when there are 60k records.</p>
  <p>Now I want to update all at one time instead of update and read it line by line.</p>
  <p>This is my previous code :</p>
  <pre><code> String cmdtxt8 = &quot;SELECT MANUF_PART, MATERIAL FROM IR_MOQANS GROUP BY MANUF_PART, MATERIAL&quot;;
              OracleDataReader odrget = db.read(cmdtxt8);
              while (odrget.Read())
              {
                  String cmdtxt9 = &quot;UPDATE IR_MOQANS SET NOTE = 'GET'
                  WHERE MANUF_PART  = '&quot; + odrget.GetString(0) + &quot;' AND MATERIAL = '&quot; + odrget.GetString(1) + &quot;' 
                  AND BUOM_MOQ  
                  &lt;= 
                  CAST ( (
                  SELECT BUOM_MOQ FROM IR_MOQANS 
                  WHERE MANUF_PART  = '&quot; + odrget.GetString(0) + &quot;' AND MATERIAL = '&quot; + odrget.GetString(1) + &quot;' 
                  AND FIXD IS NOT NULL AND ROWNUM &lt;= 1) 
                  AS DECIMAL)&quot;;
                  db.execmd(cmdtxt9);
              }
  </code></pre>
  <p>This is the query I had tried but doesn't work (I want something like this) :</p>
  <pre><code>UPDATE IR_MOQANS SET DELTA = 'G' 
  WHERE SCALE_QTY 
  &lt;= 
  (
      SELECT BUOM_MOQ  FROM IR_MOQANS WHERE (MANUF_PART, MATERIAL) 
      IN 
      (
          SELECT MANUF_PART, MATERIAL FROM IR_MOQANS GROUP BY MANUF_PART, MATERIAL
      ) 
      AND FIXD IS NOT NULL AND ROWNUM &lt;= 1
  )
  AND (MANUF_PART, MATERIAL) 
  IN 
  (
      SELECT MANUF_PART, MATERIAL FROM IR_MOQANS GROUP BY MANUF_PART, MATERIAL
  );
  </code></pre>
  <p>Below is an example of my table:</p>
  <pre><code>manuf_part  |material   |fixd    |buom_moq
  -------------------------------------------
  A           |Pen        |X       |100      
  -------------------------------------------
  A           |Pen        |        |50        
  -------------------------------------------
  A           |Pen        |        |110     
  -------------------------------------------
  A           |Bag        |X       |200     
  -------------------------------------------
  B           |Cap        |        |20       
  -------------------------------------------
  B           |Cap        |X       |50       
  -------------------------------------------
  B           |Cap        |        |70       
  -------------------------------------------
  </code></pre>
  <p>The expected result will come out like this:</p>
  <pre><code>manuf_part  |material   |fixd    |buom_moq   |note
  --------------------------------------------------------
  A           |Pen        |X       |100        |GET
  --------------------------------------------------------
  A           |Pen        |        |50         |GET
  --------------------------------------------------------
  A           |Pen        |        |110        |
  --------------------------------------------------------
  A           |Bag        |X       |200        |GET
  --------------------------------------------------------
  B           |Cap        |        |20         |GET
  --------------------------------------------------------
  B           |Cap        |X       |50         |GET
  --------------------------------------------------------
  B           |Cap        |        |70         |
  --------------------------------------------------------
  </code></pre>
  <p>Manuf_part and material MUST be grouped to get the same category. For example, (A, Pen) MUST only compare with (A, Pen).</p>