Inserting multiple rows of data into a single column-CodePudding

Absolute SQL beginner here:

I have a table 'students' with the columns 'id'(primary key), 'name' and 'mark'.

I am now trying to add the marks for a number of students. Just using multiple UPDATE statements like

    UPDATE students SET mark = 4.1 WHERE id = 1;
    UPDATE students SET mark = 1.8 WHERE id = 2;
    UPDATE students SET mark = 2.7 WHERE id = 3;
    UPDATE students SET mark = 3.5 WHERE id = 4;
    ...and so on

seems rather dull and labor intensive. What would be the most simple and elegant way to execute such an operation using MS SQL?

CodePudding user response：

First of all I would like to say that your code is perfectly fine as is. That said, in my opinion this might be a bit prettier:

UPDATE students SET mark = CASE id
    WHEN 1 THEN 4.1
    WHEN 2 THEN 1.8
    WHEN 3 THEN 2.7
    WHEN 4 THEN 3.5
END;

But again there is no need to make it more complicated than you did it. SQL is more about writing fast and accurate code than making it pretty.

Also, Welcome to Stack Overflow!

CodePudding user response：

Create a new temp table, and insert all your values into a table:

INSERT INTO grades (id, mark) VALUES ( 1, 4.1);
INSERT INTO grades (id, mark) VALUES ( 2, 1.8);
...

Now you can simply join on

UPDATE students
       JOIN grades
       ON students.id = grades.id
SET students.mark = grades.mark;

The advantage here, is except building a new grades table, your update statement does not change.