0

I have table 1 with columns

  • RunID
  • SeqNo_In_Run
  • PrimitiveID

I have table2 with columns

  • RunID
  • SeqNo_In_Run
  • some other columns

(RunID and SeqNo_In_Run) is the primary key in both tables

I want to add the PrimitiveID column to table 2 where RunID and seqno_in_run matches for every row.

I followed this answer and have written something like this:

update GPS_Snapped 
set GPS_Snapped.PrimitiveID = GPS_Snapped_New.PrimitiveID
from GPS_Snapped
inner join GPS_Snapped_New
on GPS_Snapped.RunID=GPS_Snapped_New.RunID and 
GPS_Snapped.SeqNo_In_Run=GPS_Snapped_New.SeqNo_In_Run
where GPS_Snapped.RunID=GPS_Snapped_New.RunID and 
GPS_Snapped.SeqNo_In_Run=GPS_Snapped_New.SeqNo_In_Run

but it is giving me the error near set GPS_Snapped.PrimitiveID that invalid column name PrimitiveID but I have added a new column PrimitiveID to GPS_Snapped and if i do simple update(like update table set col1 = val where condition) it's working fine. Can you help me what's wrong with my script.

Sree
  • 973
  • 2
  • 14
  • 32
  • Your table schema and your code is not coherent .. show also your exact error message – ScaisEdge May 20 '19 at 16:48
  • Is PrimitiveID in the GPS_Snapped table? My guess is that would be a no. – Sean Lange May 20 '19 at 16:49
  • @Sree . . . My guess is that you misspelled `PrimitiveID` when you added the column. Or that the column is not in `GSP_Snapped_New`. – Gordon Linoff May 20 '19 at 16:49
  • @SeanLange , @GordonLinoff, I checked them and they are fine. I have added the PrimitiveID column and I am able to update it with Joins. But when I am using join , I see this `invalid column name` error – Sree May 20 '19 at 16:52
  • Your table names doesnt match the one you use in your query. Also the field PrimitiveID isnt part of table2 – Juan Carlos Oropeza May 20 '19 at 16:54
  • You won't get an invalid column name error unless you either mistyped the column name or the column doesn't exist. Without the table structure there is nothing we can do to help. – Sean Lange May 20 '19 at 16:55

2 Answers2

0

Does GPS_Snapped_New have a Primitive_ID column? Also, you don't need a WHERE clause if you already have the join ON condition.

ovo
  • 448
  • 4
  • 16
0

Try this:

SQL DEMO

update g
set g.PrimitiveID = n.PrimitiveID
from Table1 g
inner join Table2 n
  on g.RunID = n.RunID
 and g.SeqNo_In_Run = n.SeqNo_In_Run;

btw you dont need repeat the JOIN condition on the WHERE

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Oh yes, I see that, well it's showing me the same syntax error, but i am giving it a try by running it.The data is huge, so it's still running,hopefully it will works – Sree May 20 '19 at 17:13
  • If you still get syntax error then try first with a simple select to make sure you have the field name correct. – Juan Carlos Oropeza May 20 '19 at 17:17
  • well, I don't know how but it worked!! Thanks!! May be that syntax error is my SSMS issue. Not sure though – Sree May 20 '19 at 17:21