January 11, 2019 at 12:07 am
Hi,
I have this data below;
CREATE TABLE UserTable(
UserKey int IDENTITY(1,1) NOT NULL
,FirstName varchar(100)
,LastName varchar(100)
,InsertDate date
)
CREATE TABLE LogTable(
LogID int IDENTITY(1,1) NOT NULL
,UserKey int
,BodyCount int
,InsertDate datetime
)
INSERT INTO UserTable
VALUES (13, 'John', 'Smith', '2012-01-01'), (13, 'Peter', 'John', '2012-01-01'),(13, 'Kate', 'Tommy', '2012-01-01'), (27, 'Sarah', 'Bobby', '2012-01-07'),
(27, 'King', 'Horse', '2012-01-07'), (27, 'Bongie', 'Que', '2012-01-07'),(301, 'Bona', 'Wena', '2012-01-12'), (301, 'Well', 'Done', '2012-01-12'), (301, 'Well', 'Done', '2012-01-12')
INSERT INTO LogTable
VALUES (1, 13, 0,'2012-01-01'),(2, 27, 0,'2012-01-07'),(3, 301, 0,'2012-01-12')
I want to write a SQL patch to UPDATE BodyCount in the LogTable.
I have to read count of data logged on each UserKey, then Update LogTable based on the UserKey,
Please help.
January 11, 2019 at 1:46 am
Your INSERT statements will fail unless you turn IDENTITY_INSERT on.
Why don't you just create LogTable as a view, and then it's always correct without your having to update it?
John
January 11, 2019 at 2:25 am
;WITH CTE AS
(
SELECT UserKey,
COUNT(*) Count
FROM UserTable
GROUP BY UserKey
)
UPDATE L
SET L.BodyCount = CTE.Count
FROM dbo.LogTable L
INNER JOIN CTE ON CTE.UserKey = L.UserKey
Or with even less code:UPDATE L
SET L.BodyCount = U.Count
FROM dbo.LogTable L
CROSS APPLY(SELECT COUNT(*) FROM UserTable U WHERE U.UserKey = L.UserKey) U(Count)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply