SQL Update
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL Update

  1. #1
    Join Date
    Jul 2005
    Posts
    23

    SQL Update

    can anyone tell me why i get the error

    'SQL command not properly ended'

    this sql was lifted directly from a Sybase database and seems to work OK there


    update billing_update_items
    set archive_flag = p_flag
    from billing_update_items, dummy_billings_dets
    where billing_update_items.service_id = p_service
    and billing_update_items.class_id = p_class
    and billing_update_items.update_type = 1
    and billing_update_items.billing_det_id = dummy_billings_dets.id
    and dummy_billings_dets.txs_date >= p_start_date
    and dummy_billings_dets.txs_date <= p_end_date
    and archive_flag <> p_flag;
    COMMIT;

    tia
    mark.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Cool

    Oracle does not support the UPDATE {table} SET {colum=} FROM {query} construct (syntax).
    Check oracle manual for correct syntax.

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Jul 2005
    Posts
    23

    sql update

    I assume the sql could be modified as below to achieve the same result ?

    update billing_update_items
    set archive_flag = p_flag
    WHERE billing_update_items.id IN (SELECT billing_update_items.ID
    from billing_update_items, dummy_billings_dets
    where billing_update_items.service_id = p_service
    and billing_update_items.class_id = p_class
    and billing_update_items.update_type = 1
    and billing_update_items.billing_det_id = dummy_billings_dets.id
    and dummy_billings_dets.txs_date >= p_start_date
    and dummy_billings_dets.txs_date <= p_end_date
    and archive_flag <> p_flag);

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    You could also try one of the two approaches below (untested). Which one performs better will depend on the data volumes and distribution.
    Code:
    UPDATE billing_update_items i
    SET    archive_flag = p_flag
    WHERE  service_id = p_service
    AND    class_id = p_class
    AND    update_type = 1
    AND    billing_det_id IN
           ( SELECT d.id
             FROM   dummy_billings_dets d
             WHERE  d.txs_date BETWEEN p_start_date AND p_end_date
             AND    d.archive_flag <> p_flag );
    
    UPDATE billing_update_items i
    SET    archive_flag = p_flag
    WHERE  service_id = p_service
    AND    class_id = p_class
    AND    update_type = 1
    AND    EXISTS
           ( SELECT 1
             FROM   dummy_billings_dets d
             WHERE  d.id = i.billing_det_id
             AND    d.txs_date BETWEEN p_start_date AND p_end_date
             AND    d.archive_flag <> p_flag );

Posting Permissions

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



Click Here to Expand Forum to Full Width