Just started a new job and stuck on an update

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

Transact-SQL (2012)

Just started a new job and stuck on an update

Author

Topic

oracle765
Starting Member

13 Posts

Posted - 2013-02-22 : 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

3271 Posts

Posted - 2013-02-22 : 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

13 Posts

Posted - 2013-02-22 : 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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-02-22 : 06:58:38

Try the code that Lumbago gave you. Sure looks like it should work.

If it does not, follow the link in my signature, and it will show you how to give us DDL and sample data so that we can help you better.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-02-22 : 07:44:23

Do you have a SoftwareID-column or something in these tables?

- Lumbago
My blog-> http://thefirstsql.com

oracle765
Starting Member

13 Posts

Posted - 2013-02-22 : 15:49:59

No I don't would it be better if so how would I do this to ref each table

A Lynch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources