"Merge" Style Operation with Literal Values

Merge style operation with literal values?

You can use a MERGE even if you are passing literal values. Here's an example for your issue:

CREATE PROCEDURE InsertStudentGrade(@Student INT, @Grade INT, @StartDate DATE)
AS
BEGIN;

    MERGE StudentGrade AS tbl
     USING (SELECT @Student AS Student, @Grade AS Grade, @StartDate AS StartDate) AS row
     ON tbl.Student = Row.Student AND tbl.Grade = row.Grade
    WHEN NOT MATCHED THEN
      INSERT(Student, Grade, StartDate)
       VALUES(row.Student, row.Grade, row.StartDate)
    WHEN MATCHED AND tbl.EndDate IS NULL AND tbl.StartDate != row.StartDate THEN
      UPDATE SET
        tbl.StartDate = row.StartDate;

END;

SQL Server MERGE without a source table

Try this format:

MERGE TARGET_TABLE AS I
USING (VALUES ('VALUE1','VALUE2')) as s(COL1,COL2)
ON I.COL1 = s.COL1
WHEN MATCHED THEN

You could also reference this:
"Merge" style operation with literal values?

How do I merge two tables without naming all columns?

I believe the only option you have to avoid using the column names is two separate statements:

delete from USER_COUNTERPARTY UC
      where exists
              (select null
                 from TEMP T
                where T.COUNTER_ID = UC.COUNTER_ID);

insert into USER_COUNTERPARTY UC
  select *
    from TEMP T
   where not exists
           (select null
              from USER_COUNTERPARTY UC
             where T.COUNTER_ID = UC.COUNTER_ID);

Java 8 , Merge two or more nested list

You could map each student by their id with the terminal operation collect(Collectors.toMap()), then handling the collision cases to merge your different instances of student into a new one.

At that point, when instancing a new Student, you could implement a nested stream to merge the marks of the two colliding instances. By applying a second collect(Collectors.toMap()), you could group the marks by the subject id and then handling once again the colliding cases by merging the marks' value.

In the snippet below, I've enriched your test case by adding a second Student:

List<Student> list = new ArrayList<>(List.of(
        new Student(1, new ArrayList<>(List.of(new Marks(1, 10), new Marks(2, 10)))),
        new Student(1, new ArrayList<>(List.of(new Marks(1, 15), new Marks(3, 10)))),
        new Student(2, new ArrayList<>(List.of(new Marks(1, 5), new Marks(2, 10)))),
        new Student(2, new ArrayList<>(List.of(new Marks(2, 5), new Marks(3, 10))))
));

//Temporary map
Map<Integer, Student> mapTemp = list.stream()
        .collect(Collectors.toMap(Student::getStudentId,                                        //grouping by student id
                Function.identity(),                                                            //setting as value the student
                (s1, s2) -> new Student(s1.getStudentId(), new ArrayList<>(                     //Creating a new Student whose list of marks is given by the merged marks of the two colliding students
                        Stream.concat(s1.getMarkList().stream(), s2.getMarkList().stream())     //Chaining the two lists of marks into a single stream
                                .collect(Collectors.toMap(Marks::getSubjectId,                  //Grouping by the marks by the subject id
                                        Function.identity(),                                    //Setting as value the mark
                                        (m1, m2) -> new Marks(m1.getSubjectId(), m1.getMark() + m2.getMark())))  //Handling the colliding marks by summing them together
                                .values())))                                                    //Retrieving the collection of merged marks
        );

//Result list with the merged students
List listRes = new ArrayList(mapTemp.values());
System.out.println(listRes);

Here there is also a link to test the code above:

https://ideone.com/0i6ZqG

---

Related Topics