Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2014
    Posts
    2

    Unanswered: update a table with criteria related with other tables

    I am a SQl server person, but recently start to do some oracle sqls.

    For update statement, oracle seems more complicated than t-sql for tables involved more than one.

    I attached a script that creates two tables, one is student table, one is school table, their joined key is student.schoolid= schools.school_number.
    School_number is unique in school table.

    The purpose of the query is to raise grade_level by one for non-risers of elementary school , make student next school year grade incremental by one, and next year school make it the same as this year school. The columns updated are all in the student table, but use school table to find out high grade =5 means elementary school.


    I wrote several queries that I think it will do the same thing, but I am not sure what is the most recommended way, thanks much!

    -- method 1 using inline view
    UPDATE
    (SELECT Stu.schoolid,
    Grade_Level,
    Next_School,
    Sched_Nextyeargrade
    FROM Students Stu
    INNER JOIN schools sch
    ON stu.schoolid =sch.school_number
    WHERE Enroll_Status =0
    AND Grade_Level IN (0, 1, 2, 3, 4)
    AND High_Grade =5
    ) t
    SET t.Next_School =t.Schoolid,
    t.Sched_Nextyeargrade =t.Grade_Level+1


    --method 2 use subquery in where

    UPDATE Students
    SET Next_School = Schoolid,
    Sched_Nextyeargrade =Grade_Level+1
    WHERE Enroll_Status =0
    AND Grade_Level IN (0, 1, 2, 3, 4)
    AND schoolid IN
    (SELECT school_number FROM schools WHERE High_Grade=5
    )


    -- method 3

    Merge INTO Students s USING
    (Select School_Number, High_Grade From Schools Sch Where High_Grade =5
    )Src ON (S.Schoolid =Src.School_Number AND S.Enroll_Status =0 AND s.Grade_Level IN (0, 1, 2, 3, 4))
    WHEN Matched THEN
    UPDATE SET Next_School= Schoolid, Sched_Nextyeargrade =Grade_Level+1
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2014
    Posts
    2

    udpate table with criterion that comes from another table

    This part is removed for it is the same as my original post
    Last edited by sqlfriend; 01-10-14 at 12:42.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Method 2 seems to be the most appropriate, I'd say.

Posting Permissions

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