Help needed in SQL Query-Update

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开发者_StackOverflow, 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.

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='';