Trying to update column values in SQL Server based on time of insertion and getting “The column “ID” was specificed multiple times for “p””

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.

Answer

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