Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2014

    Unanswered: Help needed with update query


    I am new to database and learning it as needed to get my workdone. I need some help writing a update query on the following table:

    table A

    Model | Category_ID| Start_DT| END_DT
    Benz-A |car1| 1900-01-01|1999-12-30
    Benz-C|car1|2000-01-01|2099-12-31 --> need to update this END_DT to 2000-01-31
    Audi-C|car2|2012-01-01|2099-12-31 --> need to update this END_DT to 2013-01-31

    On this table the category_ID should not have an end_dt twice. which means
    if i write the query : "select category_id, end_dt, count(*) as count from tableA group by category_ID, end_dt having count > 1" i should get no results, but on the dataset provided as an example, I will get car1 (with model Benz-C and Benz-D) and in car2 (with model Audi -C and Audi- D).

    Now I the task at hadn is to update these END_Dates correctly to reflect the pattern I have said above, to update *-c to the value exactly minus 1 of the start date on model *-D.
    If the data set is small I can do that with simple update by visually examination, but the data in tableA is about 50 million records, so how do we do that correclty and with ease.

    Help is greatly appreciated.


  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    This might work as a homework assignment.
    This design is flawed & should be modified to conform to Third Normal Form.
    Why is END_DT needed since it can be computed if & when required?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Liverpool, NY USA
    ill give you a hint. read up on the LAG
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

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