update query - 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

SQL Server 2008 Forums

Transact-SQL (2008)

update query

Author

Topic

shpinoza1980
Starting Member

17 Posts

Posted - 2011-01-20 : 16:56:20

Hello,

I need to update a table from another table key but i think I need to combine to inner join's to get to data and I don't know how.

here are my tables:

feedtable:
feedId int,
CompanyId int

feedDataTable:
feedID int,
CompanyName varchar,

companyTable:
companyID int,
companyName varchar,

I need to update the feedTable.CompanyID with values from companyTable.companyID.

to get it I need to go through the feedDataTable: get the relevant companyName values by joining feedDatable and feedTable ON feedDatable .feedID = feedTable.feedID
and then join feedDatatable and companyTable to get companyTable.companyID

thanks

thanks a lot

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-20 : 20:09:51

Consider that you can do this using basic Joins...what have you tried so far?

Consider this sample and see if it helps..

SELECT t1.*,t2.*, t3.*
FROM t1 inner join t2 on t1.ID = t2.ID
inner join t3 on t2.ID = t3.ID

Poor planning on your part does not constitute an emergency on my part.

shpinoza1980
Starting Member

17 Posts

Posted - 2011-01-21 : 04:01:14

I've tried some over complicated stuff....

I've tried the select statement with multiple join as suggested above and it worked great. but how do I change it into an UPDATE statement?

here is my select statement:

select t1.feedID,t1.companyID,t2.*,t3.companyName, ct3.companyID
from t1 left outer join t2 ON t1.feedID =t2.feedID
inner join t3 ON t2.feedName = t3.companyname

my goal is to use these joins in order to update t1.companyID = t3.companyID

thanks a lot

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-21 : 05:05:16

I have changed from left join to join so only matching rows will be updated.

update t1
set CompanyId = t3.CompanyId
from feedtable as t1
join feedDataTable as t2 ON t1.feedID =t2.feedID
join companyTable as t3 ON t2.feedName = t3.companyname

No, you're never too old to Yak'n'Roll if you're too young to die.

shpinoza1980
Starting Member

17 Posts

Posted - 2011-01-21 : 06:24:26

thanks all,

I've used webfred example it's working beautifully

thanks a lot

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-21 : 06:31:11

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-21 : 12:46:28

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

If you don't know anything about RDBMS, then get a copy of the simplest intro book I know --
http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905

Why did you put the word"-table" in a table name? Why aren’t you using the industry standard DUNS for the company identifier? Why is there no DRI? Let me try to fix your non-DDL:

CREATE TABLE Something_Feeds
(feed_id INTEGER NOT NULL PRIMARY KEY,,
duns CHAR(9) NOT NULL
REFERENCES Companies (duns));

CREATE TABLE Companies
(duns CHAR(9) NOT NULL PRIMARY KEY
CHECK (duns LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
company_name VARCHAR(35) NOT NULL,,

>> I need to update the FeedTable.duns with values from Companies.duns <<

No, you need to drop that silly redundancy from your schema. Use a VIEW that is always current instead of constantly accessing a disk.

CREATE VIEW Feeds_With_Names (feed_id, company_name)
AS
SELECT F.feed_id, C.company_name
FROM Something_Feeds AS F, Companies AS C
WHERE F.duns = C.duns;

You are not thinking in RDBMS yet and have to materialize everything like a deck of punch cards. Tables can be virtual.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

shpinoza1980
Starting Member

17 Posts

Posted - 2011-01-22 : 03:20:19

jcelko,

Thanks for your comments.
I understand what you are saying about the redundancy in the scheme and the non-standard naming. The thing is that I'm administrating
a SQL db structure that was poorly planed and executed, not by me, and now is intertwined with multiple applications so creating virtual tables will not help me in some cases.
the person that designed the data structure decided from some reason to name companies in the dbo.feeds [feedName] and [comapnyName] in the dbo.companies table.
In this case I have some applications that are looking for the companyID column in that specific table so I need to populate it...
this is an ad-hoc solution, and not a great one, I know. I'm rebuilding and adjusting the way these apps interact with the SQL db and will implement your comments when it is time to rethink the db structural scheme.

thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources