Select data from a table and update a second table using most recent value

Question

I have two table: Stampings and Registries.

Stampings is populate from a program that read a CSV file and do an insert into the table. Some field into the table are: name, surname, card-code and employee-freshman.

Registries contains name, surname, card-code and employee-freshman.

Now, I need a control that from Stampings select name, surname, card-code and employee-freshman and comparate with data present into Registries using card-code like unique field.

If value from Stampings and Registries not match I want to update the record in Registries with the new value using date like where cause. The update must do taking the last inserted record into Stampings.

This because a card can be from an user, but at any time it could be transferred to another person.

Can someone help me?

This is my schema:

Stampings
(
ID INT
Date DATE
Hour VARCHAR
Card-Code VARCHAR
Name VARCHAR
Surname VARCHAR
employee-freshman VARCHAR
);

Registries
(
ID INT,
Card-code VARCHAR
Name VARCHAR
Surname VARCHAR
employee-freshman VARCHAR
);

I used C# tag because here below I report the code I'm using.

Tell me if you think that logic is correct to approach.

using (SqlCommand selezionaCodiciTessera = new SqlCommand("SELECT Stampings.Card-code, Stampings.Surname, Stampings.Name, Stampings.employee-freshman FROM Stampings INNER JOIN Registries ON Stampings.Card-code = Registries.Card-code WHERE Stampings.Date = (SELECT MAX(Date) FROM Stampings WHERE Stampings.Card-code = Registries.Card-code) AND Stampings.Hour = (SELECT MAX(Hour) FROM Stampings WHERE Stampings.Card-code = Registries.Card-code) GROUP BY Stampings.Card-code, Stampings.Surname, Stampings.Name, Stampings.employee-freshman ORDER BY Stampings.Card-code;", connessione))
{
    SqlDataReader reader = selezionaCodiciTessera.ExecuteReader();

    while (reader.Read())
    {
        using (SqlConnection connessione2 = new SqlConnection(connString))
        {
            connessione2.Open();

            string query = "";

            if (reader[1].ToString().Contains("'"))
            {
                string modifica = reader[1].ToString().Replace("'", "''");

                query = "UPDATE Registries SET Surname = '" + modifica + "', Name = '" + reader[2].ToString() + "', employee-freshman = '" + reader[3].ToString() + "' WHERE Card-code = '" + reader[0].ToString() + "';";

                using (SqlCommand aggiornaOinsersci = new SqlCommand(query, connessione2))
                {
                    aggiornaOinsersci.CommandTimeout = 180;

                    try
                    {
                        aggiornaOinsersci.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        LOG("Timeout nell'esecuzione della query. " + e.ToString());
                    }
                }
            }
        }
    }
}

score 0 · Accepted Answer · answered Apr 21 '18 at 20:21

If I understand correctly, you are trying to update Registries table with the newest row of Stampings table, replacing single quotes ' (if exists) by a pair of single quotes ''.

As I pointed out in my comments you can get it by using a single SQL command, that you can execute on SSMS or by using a C# program.

I've set up a fiddle with your table schema (or similar because you have not provided columns size):

CREATE TABLE Stampings
(
    ID INT,
    Date DATE,
    Hour VARCHAR(5),
    CardCode VARCHAR(3),
    Name VARCHAR(10),
    Surname VARCHAR(20),
    EmployeeFreshman VARCHAR(10)
);

CREATE TABLE Registries
(
    ID INT,
    CardCode VARCHAR(3),
    Name VARCHAR(10),
    Surname VARCHAR(20),
    EmployeeFreshman VARCHAR(10)
);

INSERT INTO Registries VALUES
(1, 'AAA', 'Name 1', 'Surname 1', 'A'),
(2, 'BBB', 'Name 2', 'Surname 2', 'B'),
(3, 'CCC', 'Name 3', 'Surname 3', 'C'),
(4, 'DDD', 'Name 4', 'Surname 4', 'D');

INSERT INTO Stampings VALUES
(1, '20180101', '02:05', 'AAA', 'AAA Name', 'AA1 ''Surname', 'E'),
(2, '20180101', '05:05', 'AAA', 'AAA Name', 'AA2 ''Surname', 'E'),
(3, '20180101', '12:05', 'AAA', 'AAA Name', 'AA3 ''Surname', 'E'),
(4, '20180102', '12:05', 'BBB', 'BBB Name', 'BB4 Surname', 'F'),
(5, '20180102', '15:05', 'BBB', 'BBB Name', 'BB5 Surname', 'F'),
(6, '20180103', '11:05', 'CCC', 'CCC Name', 'CC6 ''Surname', 'G'),
(7, '20180103', '12:05', 'CCC', 'CCC Name', 'CC7 ''Surname', 'G'),
(8, '20180103', '13:05', 'CCC', 'CCC Name', 'CC8 ''Surname', 'G');

Now I'm using a single command to update your data in this way:

UPDATE r
SET    Surname = REPLACE(s.Surname, '''', ''''''),
       Name = s.Name,
       EmployeeFreshman = s.EmployeeFreshman
FROM   Registries r
CROSS APPLY (SELECT TOP 1
                    CardCode,
                    Surname,
                    Name,
                    EmployeeFreshman, Date, Hour
             FROM   Stampings
             WHERE  CardCode = r.CardCode
             AND    Surname LIKE '%''%'
             ORDER BY Date DESC, Hour DESC) s;

CROSS APPLY join returns the newest Stampings inserted row of each Registry, and REPLACE(s.Surname, '''', '''''') do the trick with the Surname

This is the final result:

SELECT * FROM Registries;
GO

ID | CardCode | Name     | Surname       | EmployeeFreshman
-: | :------- | :------- | :------------ | :---------------
 1 | AAA      | AAA Name | AA3 ''Surname | E               
 2 | BBB      | Name 2   | Surname 2     | B               
 3 | CCC      | CCC Name | CC8 ''Surname | G               
 4 | DDD      | Name 4   | Surname 4     | D

db<>fiddle here

I used IF because some surname contains ' char, so I verify it because Visual Studio generate me an error for SQL statement. If string contains ' I used this approach: string modifica = reader[1].ToString().Replace("'", "''"); to adapt at SQL. I worked on you query. With some adjustments works perfectly. Thank you. — Nicola_tn, Apr 23 '18 at 09:48

Select data from a table and update a second table using most recent value

1 Answers1