Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    29

    Unanswered: Update Statement

    I have a table which has fields
    a Date ,
    b Number,
    c Varchar2(4),
    d Varchar2(25),
    eVarchar2(4). The table has 78000 and odd records.
    I want to update the field e with value of field c where b is Minimum. Can anyone help me out with the SQL Statement to select the min(b).
    Thanks in Advance
    Dinesh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Update Statement

    Originally posted by dineshyadav
    I have a table which has fields
    a Date ,
    b Number,
    c Varchar2(4),
    d Varchar2(25),
    eVarchar2(4). The table has 78000 and odd records.
    I want to update the field e with value of field c where b is Minimum. Can anyone help me out with the SQL Statement to select the min(b).
    Thanks in Advance
    Dinesh
    Something like:

    UPDATE t
    SET e = (SELECT c FROM t WHERE b=(SELECT MIN(b) FROM t));

    But what if there is more than one value of c with the minimum b value?
    If you don't care, just add AND ROWNUM=1.

  3. #3
    Join Date
    May 2002
    Posts
    29
    But andrew this query will give me the c value of the min(b) of all the records in the Table where in I want the value of c for each min(b) for a particular date and particular c. Hope u have got my prob

    When I run the qry
    SELECT * FROM T
    the result is as below

    DATE_REPORT OWNER VAGAON SHIFTTIME
    -------------- ----------- ----------- -------------------
    01-APR-03 OWNER A VAG1 0800
    01-APR-03 OWNER A VAG1 1400
    01-APR-03 OWNER A VAG1 2000
    01-APR-03 OWNER A VAG2 2000
    01-APR-03 OWNER A VAG2 2030
    01-APR-03 OWNER A VAG2 2130
    01-APR-03 OWNER B VAG1 2030
    01-APR-03 OWNER B VAG1 2200
    01-APR-03 OWNER B VAG1 0030 (Showing next day, but still count in same day)
    01-APR-03 OWNER B VAG2 2100
    01-APR-03 OWNER B VAG2 0000 (Showing next day, but still count in same day)
    01-APR-03 OWNER B VAG2 0330 (Showing next day, but still count in same day)
    01-APR-03 OWNER C VAG1 1400
    01-APR-03 OWNER C VAG1 2000
    01-APR-03 OWNER C VAG1 2330
    01-APR-03 OWNER C VAG2 2130
    01-APR-03 OWNER C VAG2 2330
    01-APR-03 OWNER C VAG2 0200 (Showing next day, but still count in same day)
    02-APR-03 OWNER A VAG1 1600
    02-APR-03 OWNER A VAG1 1900
    02-APR-03 OWNER A VAG1 2300
    02-APR-03 OWNER A VAG2 2030
    02-APR-03 OWNER A VAG2 2300
    02-APR-03 OWNER A VAG2 0400 (Showing next day, but still count in same day)
    02-APR-03 OWNER B VAG1 2000
    02-APR-03 OWNER B VAG1 2200
    02-APR-03 OWNER B VAG1 0330
    02-APR-03 OWNER B VAG2 0900
    02-APR-03 OWNER B VAG2 2100
    02-APR-03 OWNER B VAG2 0500 (Showing next day, but still count in same day)
    02-APR-03 OWNER C VAG1 1500
    02-APR-03 OWNER C VAG1 2100
    02-APR-03 OWNER C VAG1 0130 (Showing next day, but still count in same day)
    02-APR-03 OWNER C VAG2 2100
    02-APR-03 OWNER c VAG2 0300 (Showing next day, but still count in same day)
    02-APR-03 OWNER c VAG2 0600 (Showing next day, but still count in same day)

    The Report format which I require is this ....
    Date_report OWNER VAGON FIRST_SHIFT
    -------------- ----------- ----------- -------------------
    01-APR-03 OWNER A VAG1 0800
    01-APR-03 OWNER A VAG2 2000
    01-APR-03 OWNER B VAG1 2030
    01-APR-03 OWNER B VAG2 2100
    01-APR-03 OWNER C VAG1 1400
    01-APR-03 OWNER C VAG2 2130
    02-APR-03 OWNER A VAG1 1600
    02-APR-03 OWNER A VAG2 2030
    02-APR-03 OWNER B VAG1 2000
    02-APR-03 OWNER B VAG2 0900
    02-APR-03 OWNER C VAG1 1500
    02-APR-03 OWNER C VAG2 2100


    Can u help me on this...
    Regards
    Dinesh

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Your original question was rather ambiguous.

    Your report would be:

    select date_report, owner, vagaon, min(shifttime)
    from t
    group by date_report, owner, vagaon;

    I don't know how you want to make 0800 earlier than 0030. If your day starts at 0800 then perhaps:

    select date_report, owner, vagaon,
    mod(min(case when shifttime < 0800 then shifttime+2400 else shifttime),2400)
    from t
    group by date_report, owner, vagaon;

    Something like that anyway!

    Where does the update of column e fit in?

  5. #5
    Join Date
    May 2002
    Posts
    29
    Andrew,
    Thanks for u help. The things is our day start by 0800. Then field e was the new Column i was planning to introduce in the table where in I can insert the first trip of the vehicles on a particular day...
    thanks a lot for ur help once again..
    Regards
    Dinesh

Posting Permissions

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