Solved

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

Posted on 2014-01-15
8
315 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 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
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

A safe way to clean winsxs folder from your windows server 2008 R2 editions
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
The viewer will learn how to dynamically set the form action using jQuery.

717 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