Update one table field from another

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 2000 Forums

Transact-SQL (2000)

Update one table field from another

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2005-01-24 : 19:18:41

I have 2 tables. I need to update a field in table 1 from a field in table
two. Here is the tricky part...

Table two has 3 fields UnitCode, UnitName and State
Table one has 3 fields QuestionID, OptionText and OptionOther

A concatinated state and unitname from table 2 was added to optionsText in
table one. They were supposed to add UnitCode to OptionOther at the same
time , but did not. How can I do this now and be accurate. Here is an
example of the tables:

Table1:
USA-MA-AA-MD-103-1-IL Lexington Market / Store MD
USA-MA-AA-MD-109-1-IL Security Square Mall / Store MD
USA-MA-AA-MD-110-1-IL St. Charles Towne Center / Store MD

Table2:
33 MD, Lexington Market / Store 0
33 MD, Security Square Mall / Store 0
33 MD, St. Charles Towne Center / Store 0

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2005-01-24 : 20:21:14

This did not work:

UPDATE FormCreator_QuestionOptions
SET FormCreator_QuestionOptions.OptionOther = franchisees.UnitCode
FROM franchisees
WHERE (FormCreator_QuestionOptions.optiontext LIKE franchisees.UnitName)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-24 : 20:26:46

I am having a hard time understand what you are asking for. You posted the sample data, but you didn't post the expected result set using that sample data.

But maybe this is what you are looking for:

UPDATE f
SET OptionOther = f.UnitCode
FROM franchisees f
INNER JOIN FormCreator_QuestionOptions fq
ON f.SomePKColumn = fq.SomePKColumn
WHERE f.optiontext LIKE f.UnitName

Tara

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2005-01-24 : 22:15:24

I need to have that data like

33 MD, Lexington Market / Store USA-MA-AA-MD-103-1-IL
33 MD, Security Square Mall / Store USA-MA-AA-MD-109-1-IL
33 MD, St. Charles Towne Center / Store USA-MA-AA-MD-110-1-IL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources