Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to delete records from a table in pure SQL?

Posted on 2013-01-21
6
Medium Priority
?
312 Views
Last Modified: 2013-01-21
I have been playing around with the insert statement in my SQL, i looked at my table and noticed that i have got 8 records for one person and 8 records for another person both of whom i used the insert statement for numerous times, obviously i only want one record for each person.

I tried to use logic, and tried DELETE * FROM Person WHERE FirstName = 'Jack Lumsdon'

&

                                             DELETE * FROM Person WHERE FirstName IS =('Jack Lumsdon')

But to no avail. I get a red squiggly line underneath the * and the error message Incorrect syntax '*'.

Any ideas of how i can get my table to delete these and bring me back down to just one record for each person.

Thanks!
0
Comment
Question by:SuperJinx
6 Comments
 
LVL 12

Expert Comment

by:mwochnick
ID: 38802666
try DELETE FROM Person WHERE FirstName = 'Jack Lumsdon'
0
 

Author Comment

by:SuperJinx
ID: 38802679
No joy! in the message's also says 0 records affected
0
 
LVL 5

Accepted Solution

by:
RehanYousaf earned 400 total points
ID: 38802713
Can you show some sample records for table PERSON and also the schema

I think what you want is to compare firstname and lastname fields

DELETE FROM Person 
WHERE FirstName = 'Jack' and LastName = 'Lumsdon'

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 8

Expert Comment

by:virtuadept
ID: 38802721
Your example has a first and a last name in the quotes.  Is the table splitting name up into two columns perhaps and you need to specifiy FirstName = 'Jack' AND LastName = 'Lumsdon' in your WHERE clause?
0
 

Author Closing Comment

by:SuperJinx
ID: 38802806
Nice one dude, that sorted it out for me!
Cheers!
0
 
LVL 12

Expert Comment

by:mwochnick
ID: 38802819
to display the table structure execute a describe statement on the person table

describe person
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

877 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