Skip to content
Advertisement

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””:

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.

Advertisement

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.

Fixed:

  1. 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.
  2. 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.
  3. Your inner-most sub-query is un-necessary, just join the table directly.
  4. 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:
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement