maltman maltman - 22 days ago 7
SQL Question

Stored procedure to update rows if exists

I have a stored procedure that I am running to insert values if they don't exist and update if they do. I have a table where the data is coming from

Table1
Name (nvarchar)
Data (int)
Timestamp (datetime)


The data looks like this

Name1 5 2016-11-16 09:46:40.490
Name2 10 2016-11-16 09:48:35.240
Name1 7 2016-11-16 09:35:24.350
Name2 8 2016-11-15 02:27:44.670


I am trying to insert the names into a new table, average out the integers, and group by day. Here is the second table

Table2
Name (nvarchar)
Data (int)
Timestamp (date)


The data looks like this. Name1 was same day so it was averaged. Name2 was different days so they were not averaged.

Name1 6 2016-11-16 00:00:00.000
Name2 10 2016-11-16 00:00:00.000
Name2 8 2016-11-15 00:00:00.000


I converted the timestamp to date so it would be easier to combine on day. My stored procedure looks like this

IF NOT EXISTS (SELECT t.Name, t.Timestamp
FROM Table2 t
JOIN Table1 a
ON t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp)
GROUP BY t.Name, t.Timestamp)

INSERT INTO Table2 (Name, Timestamp, Data)
SELECT
Name,
CAST(Timestamp AS DATE) as Date,
AVG(Data) as Average_Data
FROM Table1
GROUP BY CAST(Timestamp AS DATE), Name

ELSE
UPDATE Table1
SET
WHERE


So I first time it runs, there is no issue. The values are added and grouped correctly. However, the second time it runs, it always inserts.

My update statement had looked something like this before I deleted it

SET Name = Name, Timestamp = Timestamp, Data = Date
WHERE Name = Name, Timestamp = Timestamp


I know this is not right but it never seems to even hit the Update as the Insert always runs. If I run the Select in the If Not Exists, I see data and it should see it as exists.

I guess I need help cleaning up the If Not Exists and creating a working Update statement.

EDIT:

Updated code below

IF EXISTS(SELECT Name, Timestamp FROM Table2)
UPDATE
Table2
SET
Name = a.Name,
Timestamp = CONVERT(date, a.Timestamp),
Data = AVG(a.Data)
FROM
Table2 t
INNER JOIN
Table1 a
ON t.Name = a.Name
WHERE t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp)

ELSE
INSERT INTO Table2 (Name, Timestamp, Data)
SELECT
Name,
CAST(Timestamp AS DATE) as Date,
AVG(Data) as Average_Data
FROM Table1
GROUP BY CAST(Timestamp AS DATE), Name
END

Answer

I personally prefer to use the older style technique for what is commonly known as an "upsert". MERGE works well but is a real pain to debug problems because it does everything in one shot.

Here is the type of approach I prefer because it separates the insert and the update which provides more flexibility and is easier to debug issues. You can also switch the left join in the insert to use a NOT EXISTS with a correlated subquery but most of the time the performance difference is pretty negligible.

UPDATE t
SET 
    Name = a.Name,
    Timestamp = CONVERT(date, a.Timestamp),
    Data = AVG(a.Data)
FROM
    Table2 t
INNER JOIN
    Table1 a
ON t.Name = a.Name
WHERE t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp)


INSERT INTO Table2 (Name, Timestamp, Data)
SELECT 
    Name,
    CAST(Timestamp AS DATE) as Date,
    AVG(Data) as Average_Data
FROM Table1 a
left join Table2 t on a.Name = t.Name
where t.Name is null
GROUP BY CAST(Timestamp AS DATE)
    , Name

-- EDIT--

I didn't even notice the aggregate in your update when I posted. You can circumvent this easily with a cte.

with cte as
(
    select Name = a.Name
        , Timestamp = CONVERT(date, a.Timestamp)
        , AverageData = AVG(a.Data)
    FROM
        Table2 t
    INNER JOIN
        Table1 a
    ON t.Name = a.Name
    WHERE t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp)
)

update t
set Name = c.Name
    , Timestamp = c.Timestamp
    , Date = c.AverageData
from Table2 t
join cte c on c.Name = t.Name
    and c.Timestamp = t.Timestamp