October 22, 2009 at 4:04 pm
Can we write the below query in better way? please advice
SELECT [col1],[col2] ,[col3]
FROM [Mydb].[dbo].[table1]
where col1 ='abc' OR col1 = 'xyz' OR col2 = 'abc/cbz.abc.client'
OR col2 ='dmzxyz/cbz.xyz.client' OR col2 = 'abc/cbz.sap.client'
OR col3 = 'abc/cbz.abc.client'
-------------------------------------------
UPDATE [Mydb].[dbo].[table1]
SET [col1] = 'diff' where col1 = 'abc'
UPDATE [Mydb].[dbo].[table1]
SET [col1] = 'log' where col1 = 'xyz'
UPDATE [Mydb].[dbo].[table1]
SET [col2] = 'diff/cbz.abc.client' where col2 = 'abc/cbz.abc.client'
UPDATE [Mydb].[dbo].[table1]
SET [col2] = 'log/cbz.xyz.client' where col2 = 'dmzxyz/cbz.xyz.client'
UPDATE [Mydb].[dbo].[table1]
SET [col2] = 'diff/cbz.sap.client' where col2 = 'abc/cbz.sap.client'
UPDATE [Mydb].[dbo].[table1]
SET [col3] = 'diff/cbz.abc.client' where col2 = 'abc/cbz.abc.client'
Thanks
October 22, 2009 at 4:11 pm
SELECT [col1],[col2] ,[col3]
FROM [Mydb].[dbo].[table1]
where col1 ='abc' OR col1 = 'xyz' OR col2 = 'abc/cbz.abc.client'
OR col2 ='dmzxyz/cbz.xyz.client' OR col2 = 'abc/cbz.sap.client'
OR col3 = 'abc/cbz.abc.client'
try the below:
SELECT [col1],[col2] ,[col3]
FROM [Mydb].[dbo].[table1]
where col1 in('abc','xyz') OR col2 in('abc/cbz.abc.client',
'dmzxyz/cbz.xyz.client','abc/cbz.sap.client')
OR col3 in( 'abc/cbz.abc.client')
October 23, 2009 at 8:37 am
Performance-wise you might be better off changing your select to use Union (Union ALL if you don't mind dupes) instead of OR's as you will more likely get index seeks with the UNION'd SELECT's. Like this:
SELECT
[col1],
[col2],
[col3]
FROM
[Mydb].[dbo].[table1]
where
col1 = 'abc'
UNION
SELECT
[col1],
[col2],
[col3]
FROM
[Mydb].[dbo].[table1]
where
col1 = 'xyz'
UNION
SELECT
[col1],
[col2],
[col3]
FROM
[Mydb].[dbo].[table1]
where
col2 = 'abc/cbz.abc.client'
UNION
SELECT
[col1],
[col2],
[col3]
FROM
[Mydb].[dbo].[table1]
where
col2 = 'dmzxyz/cbz.xyz.client'
UNION
SELECT
[col1],
[col2],
[col3]
FROM
[Mydb].[dbo].[table1]
where
col2 = 'abc/cbz.sap.client'
UNION
SELECT
[col1],
[col2],
[col3]
FROM
[Mydb].[dbo].[table1]
where
col3 = 'abc/cbz.abc.client'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply