SQL Question

[toddsalcedo]

I'm trying to update a column from one table to another, but I can't get it to work. Below is the query I am using and the associated error message. Do you know of a different way to write this that might work?

SELECT * FROM tbl_issues_old, tbl_issues
UPDATE tbl_issues
SET tbl_issues.pm_name = tbl_issues_old.PMB_ASSIGNEDTO
WHERE tbl_issues.autoid = tbl_issues_old.autoid

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'tbl_issues_old' does not match with a table name or alias name used in the query.

 

[SQLSister]

Example on how to reference another table in an Update
UPDATE table1
SET field1= table2.field2
FROM table1, table2
WHERE table.id = table2.id


Questions about posting. See faq183-874

 

[toddsalcedo]

It worked, thank you very much!!!

 

[ESquared]

SQLSister, is there any reason that one shouldn't use JOIN syntax in UPDATE statements?

UPDATE T1
   SET Field1 = T2.Field2
   FROM Table1 T1 INNER JOIN Table2 T2 ON T1.ID = T2.ID

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[SQLSister]

No you can use the join, I just stole the one from BOL and modified it and their example doesn't use the join. If you are using a left join, you just about have to use the join syntax.

Questions about posting. See faq183-874

 

[ESquared]

Interesting that their example doesn't use join.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[SQLSister]

True. It took me the longest time to realize I could use the join syntax in an update statment. I just got lazy this time and didn't change it around.

Questions about posting. See faq183-874