0

I know there are quite a few posts regarding this topic, but I'm really stumped as to why my code isn't working. I simply have two tables and am trying to update a value in one table with information from the other based on a where statement. I have pretty much used this same code in other instance to do this without any issue, so I'm not sure why I'm getting an error here in this case. If anyone could offer up any assistance, I'd greatly appreciate it.

Code:

 update [DB].[dbo].[table1]

   set [DB].[dbo].[table1].[variable_name] = [DB].[dbo].[table2].[variable_name]

   where ([DB].[dbo].[table1].[Year] =  [DB].[dbo].[table2].[Year] and 
[DB].[dbo].[table1].[Id] = [DB].[dbo].[table2].[Id]);

Variable_Name is text in both tables and Year/Id are both Int.

SQLHound
  • 546
  • 11
  • 24
photec
  • 71
  • 2
  • 5

1 Answers1

2

You need to join table2 in the update statement.

UPDATE t1 
SET [table1].[variabe_name] = [table2].[variable_name]
FROM [table1] t1
INNER JOIN [table2]
    ON [table1].[variable_name] = [table2].[variable_name]
WHERE ([table1].[Year] = [table2].[Year] AND [table1].[Id] = [table2].[Id]);
Phoenix
  • 1,881
  • 5
  • 20
  • 28
SQLHound
  • 546
  • 11
  • 24
  • Reference: (http://stackoverflow.com/questions/11709043/mysql-update-column-with-value-from-another-table) – SQLHound Jun 09 '15 at 16:31
  • Thanks everyone. Will try out the code above in just a moment. Does the inner join need to be used when copying over one value? Like I said, I've used the syntax above in the past to set the values of multiple variables and have never needed to use an inner join nor have ever had this error pop up. It's only this one time where I'm trying to set a single value that this is occurring. – photec Jun 09 '15 at 16:42
  • 1
    If you are trying to set a column in Table 1 equal to a value from Table N, then you'll need to join Table N, and any other corresponding tables. – Phoenix Jun 09 '15 at 16:45
  • Thanks. Will have to do a little more digging, since the code above now says the variables from table1 can not be bound. In the code I supplied, it came back saying the variables in table 2 couldn't be bound. – photec Jun 09 '15 at 16:54
  • Thanks again everyone.I figured it out. Using my original code, I put the statement From [DB].[DBO].[Table2] under the set command and everything worked. – photec Jun 09 '15 at 17:11