Need help with 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 main key.

I have a problem when each student’s record is split into two lines.

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

I want to update the data in Student_Addressand the Student_Class fields in the row with the identifier 1from the data in the row 2, and then delete the row 2. The result in the example will 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 about 50,000 rows.

Answer 1

SQL Server, MySQL SQL, .

, 2 , MySQL, , UPDATE table SET (col1, col2) = (<a subquery with 2 columns>).

UPDATE , - MySQL.

3 , , :

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