sql - 表变量和错误 "Must declare the scalar variable"

Question

我需要为大约 20 行创建临时表或变量。

DECLARE @UserDailyStatsForCategories TABLE (
        CategoryId uniqueidentifier not null,
        [Date] date not null,
        [Unique users] int not null,
        [Returning users] int not null,
        [New users] int not null
    );

insert into @UserDailyStatsForCategories (CategoryId, [Date], [Unique users], [Returning users], [New users])                               
     select 
     @CategoryId,
      eventdate,
      count(*),
      count(case when cnt > 1 then 1 end),
      count(case when cnt = 1 then 1 end)
    from
    (
      select cast(eventtime as date) as eventdate, userid, count(*) as cnt
      from telemetrydata
      where [dbo].[TelemetryData].[DiscountId] = @DiscountId
                and [dbo].[TelemetryData].[EventName] = 'DiscountClick'
      group by cast(eventtime as date), userid
    ) date_user
    group by eventdate;

它有效，但是当我添加这个时:

update CategoryDailyStatsTemp 
set
    [CategoryDailyStatsTemp].[Unique users] = @UserDailyStatsForCategories.[Unique users],
    [CategoryDailyStatsTemp].[Returning users] = @UserDailyStatsForCategories.[Returning users],
    [CategoryDailyStatsTemp].[New users] = @UserDailyStatsForCategories.[New users]
where [CategoryDailyStatsTemp].[Id] = @UserDailyStatsForCategories.CategoryId
and [CategoryDailyStatsTemp].[Date] = @UserDailyStatsForCategories.[Date];

然后我得到了错误:

Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@UserDailyStatsForCategories".

这是怎么回事？

Answer 1

正确的语法如下:

Update  C
Set     [Unique users]    = U.[Unique users],
        [Returning users] = U.[Returning users],
        [New users]       = U.[New users]
From    CategoryDailyStatsTemp       C
Join    @UserDailyStatsForCategories U   On  U.CategoryId = C.Id
                                         And U.Date = C.Date