Updating between tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating between tables

Author

Topic

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-09-24 : 07:07:55

Team

I have one Master_Table with master data, and another Agents_Table with agents updates (same columns and properties as the Master_Table).
Agents_Table is linked to a web application where agents make updates to existing records or add new records.
Both table have a "Ref_Num" column set as the primary key.

I want to know, how should i go about updating the Master_Table with the updated comments etc.. from the Agents_table.

Appreciate your help.

Ewan Gilby

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-24 : 07:22:54

try something like this:


update a
set a.column = m.column
from agent_table as a
join master_table as m
	on m.ref_num = a.ref_num

--where 
--	...

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-24 : 07:22:55

try something like this:


update a
set a.column = m.column
from agent_table as a
join master_table as m
	on m.ref_num = a.ref_num

--where 
--	...

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-09-24 : 07:37:42

i get an error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.ref_num" could not be bound.

Ewan Gilby

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-09-24 : 07:51:51

Hi slimt,
Thanks for your help with this and putting me in the right direction. it worked when i made minor changes as below.

update m
set m.column = a.column
from agent_table as a, master_table as m
where m.Ref_Num = a.Ref_Num

Ewan Gilby

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 09:24:16

slimt_slimt's JOIN syntax is preferable - e.g. to avoid accidental Cartesian join, and also likely to help catch other types of errors during future code maintenance.

I can't see that there is any difference in your command - other than you are updating the "M" table instead of the "A" table, and you have capitalised the column name - which would be important if your database is case-sensitive (but then your table names are not the same case as your original question, so I doubt that is the case) - so perhaps the error was a typo elsewhere in the command?

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-09-24 : 09:35:08

i have removed "Join" and replaced with "," and places "where" where there is "on". hence removing the join statement altogether, which worked.
But with Join i get the mentioned error. Please advise how could i resolve this?

i want to update the master_table with values from the agent_table

Ewan Gilby

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 10:26:01

This should be fine:


update m
set m.column = a.column
from agent_table as a
    JOIN master_table as m
         ON m.Ref_Num = a.Ref_Num

but its not different from slimt_slimt's code (other than updating table M rather than A)

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2010-09-27 : 07:41:40

Hi

I guess i would have used the query incorrectly, but tried it again and it worked like a wonder. Thanks for all the help. Appreciate it

Ewan Gilby

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources