Here is my query that's throwing the "The column "ID" was specified multiple times for "p"":

update tracking.tag set
    tracking.tag.PageViewID  = p.id
    , tracking.tag.BrowserInfoID = p.BrowserInfoID
from (
    select
        t.id, t.[name], t.VisitID, t.CreatedDate, p.id, p.VisitID, p.BrowserInfoID
    from [Tracking].[Tag] as t
    inner join (
        select id, visitid, BrowserInfoID, createddate, uri
        from [tracking].[PageView]
    ) as p on abs(datediff(second, p.CreatedDate, t.createddate)) < 1 and p.VisitID = t.VisitID
    order by 1 desc
) as p

I've seen quite a few questions with the same error on SO but can't seem to see what to apply in this scenario. Any help is greatly appreciated.

CodePudding user response：

Unfortunately there is a lot broken with your statement. The error you are getting is the least of your worries and it in fact just a typo. Let me go through them.

1. The error: if you consider the following query, which is in essence what you have, how does SQL Server know which of the 2 columns in your sub-query to refer to? They are both called id! Hence if you need to select both columns you need to alias one of them to a unique name.

select id
from (
    select
        t.id, p.id
    from [Tracking].[Tag] as t
    inner join [tracking].[PageView] as p
        on ABS(datediff(second, p.CreatedDate, t.createddate)) < 1
        and p.VisitID = t.VisitID
) as p

Fixed:

select id -- Now we have a unique id column, so SQL Server knows which to select.
from (
    select
        t.id TagID, p.id
    from [Tracking].[Tag] as t
    inner join [tracking].[PageView] as p
        on ABS(datediff(second, p.CreatedDate, t.createddate)) < 1
        and p.VisitID = t.VisitID
) as p

2. You have a syntax error with your ORDER BY, you can't order a sub-query in that way as it doesn't mean anything.
3. This is a recommendation, but don't reuse the same table alias (in your case P) in multiple nested sub-queries because its really confusing to know which table/derived table you are referencing.
4. Your inner-most sub-query is un-necessary, just join the table directly.
5. Finally you aren't actually joining the table you are updating onto the query you are producing, yes you do have a join inside, but thats not the same table reference as the one you are updating. I assume thats why you have attempted to add an ORDER BY inside your sub-query despite the fact that its giving you a syntax error. In fact all you need is a simple UPDATE JOIN as follows:

-- Note you use the table alias here for the update rather than the table name
update t set
    PageViewID  = p.id
    , BrowserInfoID = p.BrowserInfoID
-- I assume this select is what you were running into issues with as you tried to test that your update was correct.
-- In this format you no longer need to alias the duplicate column names, but you could for clarity
-- select t.id TagID, t.[name], t.VisitID TagVisitId, t.CreatedDate, p.id, p.VisitID, p.BrowserInfoID
from [Tracking].[Tag] as t
inner join [tracking].[PageView] as p on abs(datediff(second, p.CreatedDate, t.createddate)) < 1 and p.VisitID = t.VisitID