[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Where Statement.

Posted on 2012-09-17
4
Medium Priority
?
327 Views
Last Modified: 2012-10-02
This is my code.  I'm seaching my table to see if any of those field contains the word 'games'.  (I want first it).  

Then another query separate 'Delete this entry based on ID number right ?

How i should run the first query search for the world 'games' in any field of my table below.  Then if they found the word 'games', i would like to run another query to delete that entry.

SELECT [id]
      ,[strFifa]
      ,[strFifaF]
      ,[strFileName]
      ,[strFileNameF]
      ,[txtDescription]
      ,[txtDescriptionF]
      ,[intSortOrder]
      ,[decRegistrationFee]
      ,[decSiteRegistrationFee]
      ,[decNonMemberRegistrationFee]
      ,[decNonMemberSiteRegistrationFee]
      ,[decDownloadFee]
      ,[decNonMemberDownloadFee]
      ,[blnActive]
      ,[blnAvailable]
      ,[blnAllMembers]
      ,[dteToRegisterForArchive]
      ,[strArchiveFileName]
      ,[strArchiveFileNameF]
      ,[blnSeries]
      ,[intTypeID]
  FROM [Football].[football].[tblFifa]
0
Comment
Question by:koila
4 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 664 total points
ID: 38407871
FROM [Football].[football].[tblFifa]
WHERE  ([strFifa] like '%games%' or  [strFifaF] like  '%games%'   )

for deleting

delete   FROM [Football].[football].[tblFifa]
WHERE  ([strFifa] like '%games%' or  [strFifaF] like  '%games%'   )
0
 
LVL 12

Assisted Solution

by:Jared_S
Jared_S earned 668 total points
ID: 38407890
aneeshattingal nailed it. Since your question implies that "games" may be in any field, just be sure to repeat
"or <field name> like '%games%' for each field that you want to search.
0
 
LVL 19

Accepted Solution

by:
Shahan Ayyub earned 668 total points
ID: 38408935
Hi!

For:

How i should run the first query search for the world 'games' in any field of my table below.  Then if they found the word 'games', i would like to run another query to delete that entry.

this script will create query that will match all columns of a table if column has "games" in any part: (you need to set @TableName parameter)

declare @tablename varchar(70)
set @tablename = 'YourTableName'
declare @query varchar(max)
set @query =(select [name] + ' like ''%games%'''  + ' OR ' from sys.columns  where object_id = object_id(@tableName) and system_type_id in(175,99,231,35,167)  for xml path('')) + '   '
set @query = ' select id from ' + @Tablename + ' where (' + replace(@query, ' OR   ','') + ')'
print @query

Open in new window


take this query to get results and once you are sure that results are OK then run this script to generate DELETE record query:

declare @tablename varchar(70)
set @tablename = 'YourTableName'
declare @query varchar(max)
set @query =(select [name] + ' like ''%games%''' + ' OR ' from sys.columns where object_id = object_id(@tableName) and system_type_id in (175,99,231,35,167) for xml path('')) + '   '
set @query = 'delete from ' + @TableName + ' where id in (' + ' select id from ' + @Tablename + ' where (' + replace(@query, ' OR   ','') + '))'
print @query

Open in new window


Hope it helps!

EDIT: modified query to avoid searching in columns like int, bit etc.
0
 
LVL 4

Author Closing Comment

by:koila
ID: 38455689
thank you.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question