query to combine Like and IN [Resolved]

Posted by Sriharim under Sql Server on 6/2/2015 | Points: 10 | Views : 705 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

In products table, category and description columns. Sometimes a product will mistakenly updated in wrong category. Example: Rice should be in Groceries category but it is in General category. Now i use below query to update the category


update products set category = 'Groceries' where description like '%egg%' or description like '%Dosa%'
or description like '%Curd%' or description like '%tomm%' or description like '%dal%' or 
description like '%rice%' or description like '%sugar%' or description like '%oil%' or description like '%chilli%'
or description like '%milk%' or description like '%boost%'

Is there query to combine the LIKE and IN operator or is there any query to reduce above query length ? (like is mandatory for to search products)

Mark as Answer if its helpful to you
---
Srihari

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 6/3/2015 [Member] [MVP] Platinum | Points: 50

0	-- Create temporary table #TempMatchList and populate with all your search strings CREATE TABLE #TempMatchList (Col VARCHAR(100)) INSERT #TempMatchList VALUES ('boost'),( 'milk'),('chilli'),('rice'), ('sugar'),('oil'), ('dal'), ('Dosa'), ('egg') SELECT * FROM Products WHERE EXISTS ( SELECT * FROM #TempMatchList WHERE Description like '%'+Col+'%' ) UPDATE Products SET Category = 'Groceries' WHERE EXISTS ( SELECT * FROM #TempMatchList WHERE Description like '%'+Col+'%' ) SELECT * FROM Products Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

-- Create temporary table #TempMatchList and populate with all your search strings
CREATE TABLE #TempMatchList (Col VARCHAR(100))
INSERT #TempMatchList VALUES ('boost'),( 'milk'),('chilli'),('rice'), ('sugar'),('oil'),  ('dal'), ('Dosa'),  ('egg')

SELECT *
FROM Products  
WHERE EXISTS
(
    SELECT *
    FROM   #TempMatchList
    WHERE Description like '%'+Col+'%' 
)

UPDATE Products 
SET Category = 'Groceries'
WHERE EXISTS
(
    SELECT *
    FROM   #TempMatchList
    WHERE Description like '%'+Col+'%' 
)

SELECT * FROM Products

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 6/3/2015 [Member] [MVP] Bronze | Points: 25

0	Hi try this sample working for me. I assume this is your answer Create Table Prod ( Prodid int Primary key identity(1,1), ProductName varchar(30), Produpid int ) Insert into Prod values('Mouse',1) Insert into Prod values('Keyboard',2) Insert into Prod values('Pc',3) Insert into Prod values('Monitor',4) Insert into Prod values('Pendrive',5) Select * from Prod Update Prod Set Produpid=101 where ProductName In('Mouse','Keyboard') Update Prod Set Produpid=102 where ProductName In('Pc','Monitor','Pendrive') Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Hi
try this sample working for me. I assume this is your answer

Create Table Prod
(
Prodid int Primary key identity(1,1),
ProductName varchar(30),
Produpid int
)

Insert into Prod values('Mouse',1)
Insert into Prod values('Keyboard',2)
Insert into Prod values('Pc',3)
Insert into Prod values('Monitor',4)
Insert into Prod values('Pendrive',5)

Select * from Prod

Update Prod Set Produpid=101 where ProductName In('Mouse','Keyboard')
Update Prod Set Produpid=102 where ProductName In('Pc','Monitor','Pendrive')

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 6/3/2015 [Member] Starter | Points: 25

0	like is must because sometimes spelling in description/product will not be correct and sometime in description will be like pendrive, pen drive, pendrive+memory like is required to search items. this is for SSIS package (Execut sql task) Mark as Answer if its helpful to you --- Srihari Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sriharim on: 6/3/2015 [Member] Starter | Points: 25

0	helpful.......thank you Mark as Answer if its helpful to you --- Srihari Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts