• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

SQL Where Statement.

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
koila
Asked:
koila
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Jared_SCommented:
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
 
Shahan AyyubSenior Software Engineer - iOSCommented:
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
 
koilaAuthor Commented:
thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now