Results 1 to 5 of 5

Thread: qryhlp

  1. #1
    Join Date
    Oct 2002
    Posts
    36

    Unanswered: qryhlp

    SELECT distinct a.gridid,
    b.dollarfrom,
    b.dollarto
    FROM spgrid_m a,
    spgrid_d b
    WHERE a.gridid = b.gridid
    AND a.groupid='451'





    GRIDI DOLLARFROM DOLLARTO
    ----- ---------- ----------
    10305 100000000 180000000
    10305 180000001 250000000
    10305 250000001 350000000
    10305 350000001 500000000




    with the abouve qry iam getting the data as above what i
    need is to update first record min balance and last record max balance how can we write the update stat


    UPDATE spgrid_d a
    SET a.dollarto = '220000000'
    WHERE a.gridid = '10303'
    AND a.dollarfrom =
    (SELECT Max(dollarfrom)
    FROM spgrid_d
    WHERE gridid = '10303'
    GROUP BY gridid)

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

    Re: qryhlp

    That last statement of yours will update the record(s) for gridid '10303' that have the max dollarfrom value and set dollarto to '220000000'.

    You can just change MAX to MIN to do a similar update for the first record(s) - a separate UPDATE statement, not both in one statement (unless you want to set them BOTH to the same amount?)

    I keep saying record(s) because for all I know there may be more than one record with the same dollarfrom amount - in fact your first query with the DISTINCT clause suggests that this may be so.

  3. #3
    Join Date
    Oct 2002
    Posts
    36

    qryhlp

    UPDATE spgrid_d
    SET dollarfrom = '100000000',
    dollarto = '400000000'
    where gridid = '10303'
    and dollarfrom=(select min(dollarfrom) from spgrid_d where gridid='10303' group by gridid)
    and dollarto =(select max(dollarto) from spgrid_d where gridid= '10303'group by gridid )

    but with the above qry it is not updating bcaz two conditions are not satisfing in single record
    first record with min should change and last record with max should change how to implement that in one
    single update statement.using inline view can we do if so how it is possible can plz suggest me...

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

    Re: qryhlp

    It can't be done in a single statement, because you want to update the dollarfrom of one record and the dollarto of another record.

    What your update above tries to do is update both the dollarfrom AND the dollarto of a single record that has dollarfrom = MIN AND dollarto = MAX. There is no such record.

    You need 2 update statements I'm afraid.

    Mind you I'm getting the impression that this is some kind of homework assignment that says "show how you can update both records at once using an in-line view"! If so, then either the teacher is mistaken, or I am! If you find out how it can be done, please post the answer here!
    (I can see how it could be done with a REAL view and an INSTEAD OF trigger, but that would really be overkill just to avoid writing UPDATE twice!)

  5. #5
    Join Date
    Oct 2002
    Posts
    36

    thanks

    hello tony Andrews

    thank u for responding me

    i updated using two updatestatements only
    i thought of doing it with one single updatestatement
    but icould not

Posting Permissions

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