Help needed in SQL Query-Update

Question

I have a Table Called Student_Details which contains the following columns: ID, Roll_No, Student_Name, Student_Address, Student_Class.

ID is the primary key.

I have a problem where the record of every student is split into two rows.

For example, the first row contains: 1, 20, john, '', '', and the second '2', '', '', 'ABCDEFG', 'A'.

I want to update the data in the Student_Address and Student_Class fields in the row with ID 1 from the data in row 2, and then delete row 2. The result in the example would be 1,20,'john', 'ABCDEFG', 'A'.

Is there any way to do this? I do not want to use a cursor, because the database has around 50000 rows.

Answer 1

Do not have SQL Server installed, but made this work in MySQL using standard SQL syntax so it should work with you too.

Note, that I created 2 update-s for each column as MySQL does not seem to support the UPDATE table SET (col1, col2) = (<a subquery with 2 columns>) syntax.

I also wrap UPDATE subquery in another one, which is only necessary because of some MySQL restriction.

The 3 queries should probably be run in one transaction:

update student_details s0 set s0.student_address = 
(
 select student_address from (
  select s1.id, s1.roll_no, s2.student_address
  from student_details s1
  join student_details s2
   on s2.id=s1.id+1
 ) sub where sub.id = s0.id
)
where s0.roll_no <> '';

update student_details s0 set s0.student_class = 
(
 select student_class from (
  select s1.id, s1.roll_no, s2.student_class
  from student_details s1
  join student_details s2
   on s2.id=s1.id+1
 ) sub where sub.id = s0.id
)
where s0.roll_no <> '';

delete from student_details where roll_no='';