Stuck on an update query for my boss
-
Thursday, February 21, 2013 10:59 AM
I am trying to update a table for my boss to cleans data which shows data like the following
'Microsoft Office-2003' , 'Microsoft 2003', 'Microsoft Office 2003' in one column and various other column names so for instance if it is equal to any of those names then it should be changed to just 'Office 2003 Professional'I have thousands of rows for lots of different criteria and I am curius if it is possible to do it a quicker way then case"ing" every eventuality together like in my query below. There would end up being thousands of "WHEN", "THEN" statements
use TESTdata
Go
drop table bigtableSelect * Into BigTable From dbo.The_Big_Kahuna
Select Count(*) from dbo.BigTableUpdate dbo.BigTable
set software_name_raw =
( CASEWHEN dbo.BigTable.Software_Name_Raw = 'Microsoft Office 2003' Then 'Office 2003 Professional'
WHEN dbo.BigTable.Software_Name_Raw = 'Microsoft Office 2003' Then 'Office 2003 Professional'
WHEN dbo.BigTable.Software_Name_Raw = 'Microsoft 2003 Office' Then 'Office 2003 Professional'
WHEN dbo.BigTable.Software_Name_Raw = 'Microsoft-Office 2003' Then 'Office 2003 Professional'
WHEN dbo.BigTable.Software_Name_Raw = 'Microsoft Office-2003' Then 'Office 2003 Professional'
WHEN dbo.BigTable.Software_Name_Raw = 'Microsoft Office 2003 Standard' Then 'Office 2003 Standard'
WHEN dbo.BigTable.Software_Name_Raw = 'Microsoft Office Standard 2003' Then 'Office 2003 Standard'
WHEN dbo.BigTable.Software_Name_Raw = 'Microsoft Visual C++ 2008 Redistributable - x86 9.0.21022.218' Then 'Visual C++ 2008 Redistributable (32bit)'
WHEN dbo.BigTable.Software_Name_Raw = 'Microsoft Sync Framework 2.1 Database Providers (x86) ENU' Then 'Sync Framework Database Providers (32bit)'
WHEN dbo.BigTable.Software_Name_Raw = 'Microsoft Office Visio 2002 (Exe)' Then 'Visio 2002 (Exe)'
ELSE 'NEVER UPDATED THIS ROW'
END
)
OUTPUT Deleted.software_name_raw AS BeforeValue, -- Value before update
Inserted.software_name_raw AS AfterValue --Updated value
SELECT *
FROM dbo.BigTable
WHERE dbo.BigTable.Software_Name_Raw = 'NEVER UPDATED THIS ROW'
All Replies
-
Thursday, February 21, 2013 11:14 AM You may create a temp table(it can be even physical table) with Dummyvalue and ActualValue columns. ITs somthing like a mapping table for you. Then you can join the temp table with your BigTable.Software_Name_Raw and update with the ActualValue column.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
- Proposed As Answer by Michael Samteladze Thursday, February 21, 2013 1:52 PM
-
Thursday, February 21, 2013 11:26 AM
Try something like this,
declare @table table(name varchar(max)) insert into @table select 'Microsoft Office 2003' union select 'Microsoft Office 2003' union select 'Microsoft 2003 Office' union select 'Microsoft-Office 2003' union select 'Microsoft Office-2003'union select 'Microsoft Office 2003 Standard' union select 'Microsoft Office Standard 2003' union select 'Microsoft Visual C++ 2008 Redistributable - x86 9.0.21022.218' union select 'Microsoft Sync Framework 2.1 Database Providers (x86) ENU' union select 'Microsoft Office Visio 2002 (Exe)' declare @table2 table (Name varchar (max),updated varchar(max)) insert into @table2 select name,'Office 2003 Professional' UpdatedName from @table where name like '%Microsoft%Office%2003%' and name not like '%Standard%' union all select name,'Office 2003 Standard' UpdatedName from @table where name like '%Standard%' select * from @table select * from @table2
Now try to lookup the second table (@table2) and update the first table..
Regards,
Dineshkumar
Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you -
Thursday, February 21, 2013 11:48 AMThe nion wold not work, I wold have to write over 400,000 of them for every different possibility
-
Thursday, February 21, 2013 11:58 AM
Hi Latheesh
Thats sounds like a way around I will try this tomorrow when I get to work. So would it be something like this
table 1 = col1(Microsoft Office 2003 Professional) "Raw Value", col2(Office 2003 Professional) "What it should be"
col1(Microsoft Office Professional 2003) "Raw Value", col2(Office 2003 Professional) "What it should be"
exact copy
table 2 = col1(Microsoft Office 2003 Professional) "Raw Value", col2(Office 2003 Professional) "What it should be"
col1(Microsoft Office Professional 2003) "Raw Value", col2(Office 2003 Professional) "What it should be"
if table 1.col1 "Raw Value" = table 2.col1 "Raw Value" then
update table 1.col1 "Raw Value" with table 2.col2 "Actual Value"
am i thinking correctly here if so how would I put this into a query as i am still fairly new to sql?
thanks
-
Friday, February 22, 2013 5:13 AM
I still cannot figure this out all i am trying to do
is update table1 column1's value if table2 columns 1 values match with table2 columns2 value
table 1 containscolumn 1
microsoft outlook 2007
microsoft 2007 outlook
microsoft out look 2007
microsoft 2007 office
microsoft office 2007
table 2 containscolumn 1
microsoft outlook 2007
microsoft 2007 outlook
microsoft out look 2007
microsoft 2007 office
microsoft office 2007
and in column 2
outlook 2007
office 2007
so the final result should be in table 1, column 1 should say
outlook 2007
office 2007 thats if they match
the query i have is which i do not know if this is the right way in thinking is
-------------------------------------------------------
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
.png)
