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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy