SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question about deleting records

    Hi there,
    This is probably a really easy one for most of you, but I have no idea how to go about this.
    In a database I'm currently working on I have a column called "ItemNumber" which contains the item number(Though it contains numbers and letters in most cases) of products contained in the database. any products with an item number of less than 15 characters long is invalid and I need to delete the entire record.

    I assume I can just run a SQL command in phpmyadmin to delete all records where the item number contains less than 15 characters, but I have no idea what SQL command I would need to use to do this.

    I hope I was detailed enough in explaining what I'm trying to accomplish here.

    thanks in advance!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    DELETE
      FROM itemtable
     WHERE LENGTH(itemnumber) < 15
    that wasn't too hard, was it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a ton! that worked perfectly!


    Okay, one more quick question for now...

    I also need to delete any records with an item number that the first two characters are NOT Numeric/ Alpha. (number, letter)

    for example this would be a valid itemnumber: 1ABRK0COLO100WW

    this would be an invalid itemnumber and need to be deleted because the first two characters are numbers: 02COM0ASMB0000

    and this would also be an invalid itemnumber and need to be deleted because the first two characters are letters: KKHPDCUTW290000

    thanks again!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    DELETE
      FROM itemtable
     WHERE itemnumber REGEXP '^[^[:digit:]^[:alpha:]]'
    untested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmm, that one didn't seem to work for me. I made sure I added some item numbers that started with 2 letters and some that started with two numbers and it said "0 records deleted"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
     WHERE itemnumber REGEXP '^([^[:digit:]]|.[^[:alpha:]])'
    tested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    delete
      from itemtable
     where itemnumber not regexp '^[[:digit:][:alpha:]]'

  8. #8
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still doesn't seem to be deleting anything...

    Deleted rows: 0

  9. #9
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    correction the one you posted r937 worked perfect!

  10. #10
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay and one more final question, and I'm done for now, I swear. hahaha

    Is there a way to combine all this without having to run 3 seperate commands?

    PHP Code:
    DELETE 
      FROM 
    `products
     
    WHERE LENGTH(itemnumber) < 15 
    PHP Code:
    DELETE 
      FROM 
    `products
     
    WHERE LENGTH(itemnumber) > 15 
    PHP Code:
    DELETE
      FROM 
    `products`
     
    WHERE itemnumber REGEXP '^([^[:digit:]]|.[^[:alpha:]])' 

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, you can combine them with ORs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like this perhaps?

    Code:
    DELETE 
      FROM `products` 
     WHERE LENGTH(itemnumber) < 15
      OR
     WHERE LENGTH(itemnumber) > 15 
      OR
     WHERE itemnumber REGEXP '^([^[:digit:]]|.[^[:alpha:]])'

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would certainly give that a try and see what it does

    tip: take a backup first

    -------------------------------
    Q: i lost my data! halp!
    A: did you take a backup?
    Q: no
    A: that's bad judgment
    Q: oh? and where does good judgment come from?
    A: experience
    Q: and where does experience come from?
    A: bad judgment
    -------------------------------

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I made a backup first

    tried to run that and it said I had a syntax error...

  15. #15
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay I took out the last 2 WHERE's and it worked


    Thanks so much!!

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    see? that's the way!! you learn so much better by trying things yourself than asking other people how to do it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks again


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •