Results 1 to 2 of 2

Thread: Update query

  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: Update query

    Hi,

    Can the below update query be written in better way.

    PHP Code:
    Update   INVOICE IL
      Set LINE_OPEN_DATE
    =
          ( 
    Select min(ORDER_DATE)
             
    From   BOOKING_LINE BL
              Where BL
    .DB_CODE=IL.DB_CODE
               
    And  BL.ORDER_NUMBER=IL.ORDER_NUMBER
               
    And  BL.ORDER_LINE=IL.INV_LINE
               
    And  BL.ITEM_ID=IL.ITEM_ID)
      
    where 
       IL
    .LINE_OPEN_DATE <>
       ( 
    Select min(ORDER_DATE)
             
    From   BOOKING_LINE BL
              Where BL
    .DB_CODE=IL.DB_CODE
               
    And  BL.ORDER_NUMBER=IL.ORDER_NUMBER
               
    And  BL.ORDER_LINE=IL.INV_LINE
               
    And  BL.ITEM_ID=IL.ITEM_ID)
      And 
    EXISTS
        
    Select NULL
             From   BOOKING_LINE BL
              Where BL
    .DB_CODE=IL.DB_CODE
               
    And  BL.ORDER_NUMBER=IL.ORDER_NUMBER
               
    And  BL.ORDER_LINE=IL.INV_LINE
               
    And  BL.ITEM_ID=IL.ITEM_ID); 
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The EXISTS is redundant, so it simplifies to:
    Code:
     Update   INVOICE IL
      Set LINE_OPEN_DATE=
          ( Select min(ORDER_DATE)
             From   BOOKING_LINE BL
              Where BL.DB_CODE=IL.DB_CODE
               And  BL.ORDER_NUMBER=IL.ORDER_NUMBER
               And  BL.ORDER_LINE=IL.INV_LINE
               And  BL.ITEM_ID=IL.ITEM_ID)
      where
       IL.LINE_OPEN_DATE <>
       ( Select min(ORDER_DATE)
             From   BOOKING_LINE BL
              Where BL.DB_CODE=IL.DB_CODE
               And  BL.ORDER_NUMBER=IL.ORDER_NUMBER
               And  BL.ORDER_LINE=IL.INV_LINE
               And  BL.ITEM_ID=IL.ITEM_ID)
    (If the EXISTS would have returned FALSE, then the <> would not have returned TRUE either).

Posting Permissions

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