Update Table based on the Key from another table

• mediacommentry

  Old Hand

  Points: 348

  More actions

  January 11, 2019 at 12:07 am

  #370063

  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.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 11, 2019 at 1:46 am

  #2018667

  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

• Jonathan AC Roberts

  SSCoach

  Points: 18253

  More actions

  January 11, 2019 at 2:25 am

  #2018671

  ;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)