?
Solved

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

Posted on 2014-01-15
8
Medium Priority
?
316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Accepted Solution

by:
tel2 earned 1500 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 300 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
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 200 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

800 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