Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>Total rewrite</strong></p> <p>This is a complete rewrite to fit my example to the OQ's revised question. Unfortunately Manish has not actually run my original solution otherwise they would realise the following assertion is wrong:</p> <blockquote> <p>Your solution returns all those student who have 100 marks in any subject. It does not exclude records which have non 100 marks.</p> </blockquote> <p>Here are six students and their marks. </p> <pre><code>SQL&gt; select * from student 2 / ID RANK ---------- ---------- 1 normal 2 normal 3 normal 4 normal 5 normal 6 normal 6 rows selected. SQL&gt; select * from marks 2 / COURSE_ID STUDENT_ID MARK ---------- ---------- ---------- 1 1 100 2 1 100 1 2 100 2 2 99 1 4 100 2 5 99 1 6 56 2 6 99 8 rows selected. SQL&gt; </code></pre> <p>Student #1 has two courses with marks of 100. Student #4 has just the one course but with with a mark of 100. Student #2 has a mark of 100 in one course but only 99 in the other course they have taken. None of the other students scored 100 in any course. Which students will be awarded a 'merit?</p> <pre><code>SQL&gt; update student s 2 set s.rank = 'merit' 3 where exists ( select null 4 from marks m 5 where m.student_id = s.id 6 and m.mark = 100 ) 7 and not exists ( select null 8 from marks m 9 where m.student_id = s.id 10 and m.mark != 100) 11 / 2 rows updated. SQL&gt; SQL&gt; select * from student 2 / ID RANK ---------- ---------- 1 merit 2 normal 3 normal 4 merit 5 normal 6 normal 6 rows selected. SQL&gt; </code></pre> <p>And lo! Only those students with 100 marks in <em>all</em> their courses have been updated. Never underestimate the power of an <code>AND</code>.</p> <p>So the teaching is: an ounce of testing is worth sixteen tons of supposition.</p>
    singulars
    1. This table or related slice is empty.
    1. POOracle SQL: update table conditionally based on values in another table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAPC
    1. USAPC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POOracle SQL: update table conditionally based on values in another table
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you for your answer, but it does not completely solves my problem. My exact problem is like : I have two tables STUDENTS, MARKS. MARKS has a FK to STUDENTS named STUDENT_ID.MARKS has number of rows for a STUDENT record, depending on subject, and has a column named MARKS_OBTAINED. I have to check that if MARKS_OBTAINED for one student for every subject(i.e. all his records in MARKS) have value 100, then update STUDENT table's column RANK to a value 'Merit'. Your solution returns all those student who have 100 marks in any subject. It does not exclude records which have non 100 marks.
      singulars
      1. PO
      1. USWalker
    2. COBefore doing an update, i tried this select statement : SELECT MARKS.STUDENT_ID, MARKS.MARK FROM MARKS WHERE EXISTS (SELECT * FROM STUDENT WHERE MARKS.STUDENT_ID = STUDENT.ID AND MARKS.MARK = 100) AND NOT EXISTS (SELECT * FROM STUDENT WHERE MARKS.STUDENT_ID = STUDENT.ID AND MARKS.MARK != 100) This returned 4 rows : 1, 100 ;1, 100; 2, 100; 4, 100 So, i doubted, it may update STUDENT with ID 2 as well.
      singulars
      1. PO
      1. USWalker
    3. COLater, i checked with this query : SELECT S.ID FROM STUDENT S WHERE EXISTS (SELECT * FROM MARKS M WHERE M.STUDENT_ID = S.ID AND M.MARK = 100) AND NOT EXISTS (SELECT * FROM MARKS M WHERE M.STUDENT_ID = S.ID AND M.MARK != 100) and it returned only two rows 1 4 So the update should work correct. Thank you for your time and reply.
      singulars
      1. PO
      1. USWalker
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload