| Author |
 Topic  |
|
|
oracle765
Starting Member
Australia
13 Posts |
 Posted - 02/22/2013 : 00:26:00
|
Hi all
I am using sqlserver 2008
Im new to sqlserver and I need to do an update statement for my boss, I have worked all week but still cannot figure it out
ll i am trying to do
is update table1 column1's value if table2 columns 1 values match with table2 columns2 value bare in mind there are thousands of rows
example
table 1 contains:
column 1:
microsoft outlook 2007,microsoft 2007 outlook,microsoft out look 2007,microsoft 2007 office,microsoft office 2007 plus lots more
table 2 contains:
column 1:
microsoft outlook 2007,microsoft 2007 outlook,microsoft out look 2007,microsoft 2007 office,microsoft office 2007 plus lots more
and in column 2:
outlook 2007,office 2007 plus lots more
so the final result should be!: in table 1, column 1 should say
outlook 2007
office 2007 which is table 2s expected value
the query i have is as follows i do not know if this is the right way in thinking
-------------------------------------------------------
with C as
(
select distinct RTRIM(LTRIM(TGT.software_name_raw)) as Target_Name,
RTRIM(LTRIM(SRC.software_name_raw)) as Source_Name
from dbo.BigTable as TGT
INNER JOIN dbo.RefTable as SRC
on TGT.software_name_raw = SRC.software_name_raw
)
update C
set Target_Name = Source_Name
--------------------------------------------------------
it is also producing an error saying
Msg 4406, Level 16, State 1, Line 14
Update or insert of view or function 'C' failed because it contains a derived or constant field.
Please help
A Lynch |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 02/22/2013 : 03:41:38
|
I'm a little confused about the requirements but I think this is actually really simple. Mind you that the value for the join condition (on TGT.software_name_raw = SRC.software_name_raw) must be equal in both tables for the join to work properly
update TGT
set TGT.column_with_wrong_value = RTRIM(LTRIM(SRC.column_with_correct_value))
from dbo.BigTable as TGT
INNER JOIN dbo.RefTable as SRC
on TGT.software_name_raw = SRC.software_name_raw
- Lumbago
My blog-> http://thefirstsql.com |
 |
|
|
oracle765
Starting Member
Australia
13 Posts |
Posted - 02/22/2013 : 05:22:20
|
Thanks lumbago
firstly thanks for your prompt response I have noticed one error in that I am not trying to update with the software name amended when i should be
secondly I hope this can explain it further
if table 1's software_name_raw = table2's software_name_raw then
I want to update table 1s rows with table 2's software_name_amended value of what it should be.
for example the 4th row should change to Visio 2003 Viewer
the 6th row should change to Visio 2007
if there is no match then just leave table1's software_name_raw value as it is
I hope this helps you guys as I am now starting to confuse myself further lol
TABLE1-COL1 called software_name_raw
Microsoft Office Visio Viewer 2003 (English)
Microsoft Office Visio 2003 Step by Step
Microsoft Office Visio 2003 Step by Step
Microsoft Office Visio Viewer 2003 (English)
Microsoft Office Visio Viewer 2003 (English)
Microsoft Office Visio 2007 (Exe)
Microsoft® Office Visio® 2007
Microsoft® Office Visio® 2007
Microsoft Office Visio 2007 Professional Edition
Microsoft Office Visio Professional 2007
Microsoft Office Visio Viewer 2007
Microsoft Office Visio Viewer 2007
Microsoft Office Visio Viewer 2007
Microsoft Office Visio Viewer 2007
Microsoft Office Visio 2010
Microsoft Office Visio 2010
Microsoft Office Visio 2010 (Exe)
Microsoft Office Visio 2010 Premium Edition
Microsoft Office Visio 2010 Professional Edition
TABLE2-COL1 also called software_name_raw TABLE2-COL2 called software_name_ammended
Microsoft Office Visio Viewer 2003 (English), Visio 2003 Viewer
Microsoft Office Visio 2003 Step by Step, Visio 2003 Step by Step
Microsoft Office Visio 2003 Step by Step, Visio 2003 Step by Step
Microsoft Office Visio Viewer 2003 (English), Visio 2003 Viewer
Microsoft Office Visio Viewer 2003 (English), Visio 2003 Viewer
Microsoft Office Visio 2007 (Exe), Visio 2007
Microsoft® Office Visio® 2007, Visio 2007
Microsoft® Office Visio® 2007, Visio 2007
Microsoft Office Visio 2007 Professional Edition, Visio 2007 Professional
Microsoft Office Visio Professional 2007, Visio 2007 Professional
Microsoft Office Visio Viewer 2007, Visio 2007 Viewer
Microsoft Office Visio Viewer 2007, Visio 2007 Viewer
Microsoft Office Visio Viewer 2007, Visio 2007 Viewer
Microsoft Office Visio Viewer 2007, Visio 2007 Viewer
Microsoft Office Visio 2010, Visio 2010
Microsoft Office Visio 2010, Visio 2010
Microsoft Office Visio 2010 (Exe), Visio 2010
Microsoft Office Visio 2010 Premium Edition, Visio 2010 Premium
Microsoft Office Visio 2010 Professional Edition, Visio 2010 Professional
A Lynch |
Edited by - oracle765 on 02/22/2013 05:29:43 |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 02/22/2013 : 07:44:23
|
Do you have a SoftwareID-column or something in these tables?
- Lumbago
My blog-> http://thefirstsql.com |
|
|
|
oracle765
Starting Member
Australia
13 Posts |
Posted - 02/22/2013 : 15:49:59
|
No I don't would it be better if so how would I do this to ref each table
A Lynch |
|
|
| |
Topic |
|
|
|
Just started a new job and stuck on an update
