Solved

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

Posted on 2014-01-15
8
301 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 11

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 11

Expert Comment

by:tel2
ID: 39787028
Good points, John.
Thanks.
0
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
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…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…
The viewer will learn how to count occurrences of each item in an array.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now