Results 1 to 3 of 3

Thread: Query Help

  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: Query Help

    Can someone tell me is there better way to write following query :--
    maintab table updated from secondtab table

    Maintab
    key cola qty qty1
    k1 row1 10 2
    k1 row2 20 3
    k2 row1 100 2

    secondtab
    Sno key cola qty
    1 k1 row1 2
    1 k1 row2 3
    2 k2 row1 6

    update maintab ma set ma.qty = ma.qty + (select SI1.qty from secondtab SI1 where SI1.sno = '1' and SI1.cola = ma.cola and SI1.key = ma.key) ,
    ma.qty1 = ma.qty1 - (select SI2.qty from secondtab SI2 where SI2.sno = '1' and SI2.cola = ma.cola and SI2.key = ma.key)
    where (ma.key,ma.cola) in (select so.key, So.cola from secondtab So where So.sno = '1')

    Expected Result
    Maintab
    key cola qty qty1
    k1 row1 12 0 Record updated
    k1 row2 23 0 Record updated
    k2 row1 100 2 Not effected

    My concern is to update the qty back to Maintab 2 columns, i am running subquery twice

    Please advise, Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Consider using the MERGE statement instead.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2008
    Posts
    48
    Thank you Nick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •