SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Just started a new job and stuck on an update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oracle765
Starting Member

Australia
13 Posts

Posted - 02/22/2013 :  00:26:00  Show Profile  Reply with Quote
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
3271 Posts

Posted - 02/22/2013 :  03:41:38  Show Profile  Reply with Quote
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
Go to Top of Page

oracle765
Starting Member

Australia
13 Posts

Posted - 02/22/2013 :  05:22:20  Show Profile  Reply with Quote
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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 02/22/2013 :  06:58:38  Show Profile  Reply with Quote
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

Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 02/22/2013 :  07:44:23  Show Profile  Reply with Quote
Do you have a SoftwareID-column or something in these tables?

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

oracle765
Starting Member

Australia
13 Posts

Posted - 02/22/2013 :  15:49:59  Show Profile  Reply with Quote
No I don't would it be better if so how would I do this to ref each table

A Lynch
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.89 seconds. Powered By: Snitz Forums 2000