Solved

How to write a SQL script to delete rows in SQL table based on parameter?

Posted on 2014-01-15
8
311 Views
Last Modified: 2014-02-13
I have a database that runs in SQL. I use SQL Management Studio 2008 R2 to view the tables. In my database, on one of my tables. I have a table called AP.VEN. That table contains all my vendors. If i right click on the table, and click on Edit Top 200 Rows, i can see all the columns with data in that table. One of the columns is Phone. That has all the phone numbers for my vendors. I need to delete all my vendors that DO NOT contain the numbers 630. I know the risk is there is a phone number that has 630 in the middle and it might be deleted, but that doesn't matter to me. What would the script be to delete all vendors where the phone number contains 630? To summarize, Table is AP.VEN, column is PHONE. I want to delete all vendor records that have 630 in the PHONE column of the AP.VEN table. Thanks in advance!!!
0
Comment
Question by:brasiman
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Accepted Solution

by:
tel2 earned 375 total points
ID: 39784080
Hi brasiman,

How about running this SQL:
    DELETE FROM 'ap.ven' WHERE phone NOT LIKE '%630%'

Before running the above, I suggest you list the rows it will delete, and make sure they look reasonable, by running this SQL:
    SELECT * FROM 'ap.ven' WHERE phone NOT LIKE '%630%'

PS: I don't know SQL Management Studio, so the above syntax might not be 100% correct.  Try double-quotes instead of single, if appropriate.  Change all to upper-case if you like.

AP.VEN doesn't look like a table name to me.  It looks like <table>.<field>
If you mean AP is the table, then you could replace my references to 'ap.ven', with ap or AP (no quotes needed).
0
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 75 total points
ID: 39786657
Careful, the original request has conflicting statements:

I need to delete all my vendors that DO NOT contain the numbers 630.

What would the script be to delete all vendors where the phone number contains 630?

If ap is the table-owner then ap.ven is correct, however, if the tablename contains an embedded special-character (period) then use brackets, i.e., [ap.ven] or double-quotes "ap.ven"
0
 
LVL 12

Expert Comment

by:tel2
ID: 39787028
Good points, John.
Thanks.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 50 total points
ID: 39787931
Regarding the AP in AP.VEN confusion: as this is SQL Server, most likely AP is the schema in which the VEN table resides.  So the following query deletes all records where phone contains 630:

DELETE FROM AP.VEN WHERE phone LIKE '%630%'

Open in new window

For completeness sake: it is possible to create a table with a dot in its name but that would be really bad practice.  However, should one want to do so, running the following statement will create a table called duh.tab.  The statement doesn't mention a schema name so the table is created in the default schema, possibly dbo:

create table [duh.tab] ( <columns> )

Open in new window

In such a case, a clean way to reference the table is dbo.[duh.tab].
0
 

Author Comment

by:brasiman
ID: 39847790
Good catch John_Vidmar, i need to delete all vendors that DO NOT contain 630. So, in other words, anything that has 630, i want to keep. If it doesn't have 630 i want to delete it.
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39848254
tel2's answer above is correct about using WHERE phone NOT LIKE '%630%'
0
 

Author Comment

by:brasiman
ID: 39853638
Ok I will try that today and let you know. Thanks!
0
 

Author Closing Comment

by:brasiman
ID: 39856402
Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Windows mapped drive communications - Secure? 5 48
Convert .csv to tab delimited text 11 37
File Screening 1 16
powershell switch statement 7 15
You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

821 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