Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-15
8
Medium Priority
?
318 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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses

610 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