How To Update Joined 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

How To Update Joined Tables

Author

Topic

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-08-20 : 04:40:40

Hello clever peeps

Does anyone know how to update using a joined set of tables

I have two tables with a common primary key. Table compound has the following fields

compound_id (int primary key)
compound (varchar(100)
hazard_info (varchar(200)

Table hazard has the following fields

compound_id (int primary key matches other table)
hazard_phrase (varchar(200)

I would like to update the compound table and insert the hazard_phrase from the hazard table into the hazard_info of the compound table.

I am using SQL Server 8

Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-20 : 04:59:35

http://weblogs.sqlteam.com/mladenp/archive/2007/08/19/60292.aspx

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-08-20 : 05:14:28

The example shows the following update

-- demonstrate with update
UPDATE t3
SET t3_text = 'we just updated the t3 table'
FROM t1
JOIN t2 ON t1.id = t2.t1Id
JOIN t3 ON t1.id = t3.t1Id
LEFT JOIN t4 ON t3.id = t4.t3Id
WHERE t4.id = 2

In the example that I need resolving I need to update the compound tables field hazard_info with the contents of the hazards table hazard_phrase where all compound_ids match in both tables. I am new to SQL but I dont think this example demonstrates what I need. I am thick tho lol!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-20 : 05:31:03

the example shows the methodology that you should use not the exact problem you have.
first create the select that gets the data you need.
have the column you want to update to and the column you want to update from in the returned resultset.
then just change the select part to the update part without changing the from part.
and that's it.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

milena
Starting Member

16 Posts

Posted - 2008-08-20 : 07:09:09

Try this:
Update Compound
set hazard_info=h.hazard_phrase
from Compound C
join
Hazard H
on
C.Compound_ID=H.Compound_ID
where
C.Compound_ID=H.Compound_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources