SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query to delete orphans

    I have 2 database tables
    one called lyrics and the other called text

    the id for each lyric is the same id for each text records but I have noticed there are some lyrics that has no text records...

    what I need is help with what query I could run that will detect which lyrics has no text and remove the lyric record...

    thanks in advance...

  2. #2
    SitePoint Wizard Young Twig's Avatar
    Join Date
    Dec 2003
    Location
    Albany, New York
    Posts
    1,355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like this, maybe?

    DELETE FROM lyrics WHERE id NOT IN (SELECT id FROM text)

    I'd back up your DB a few times before trying that. I've been known to empty tables with my DELETE queries.

    Edit:

    Try the SQL forum. There are some geniuses in there.

  3. #3
    SitePoint Wizard holmescreek's Avatar
    Join Date
    Mar 2001
    Location
    Northwest Florida
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation

    select lyrics.id as lid, text from lyrics,text where trim(text) = '';

    Test this in MySQL first. And look at the data, it should return the record ids from lyrics where the text in the text table is blank, or null

    if so, BACK UP YOUR DATABASE then proceed...

    ----------

    $sql = "select lyrics.id as lid, text from lyrics,text where trim(text) = ''";

    $results = Query_Database($sql); // run the query from php


    while($arow = mysql_fetch_array($results)){

    $no_lyric_id = $arow['lid'];

    $del_recor = Query_Database("delete from lyrics where id='$no_lyric_id'");

    }
    intragenesis, llc professional web & graphic design

  4. #4
    SitePoint Wizard holmescreek's Avatar
    Join Date
    Mar 2001
    Location
    Northwest Florida
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    YoungTwig has a good solution as well. From my example, I was coming from a viewpoint that you had table 1 with the songs and table 2 with the lyrics and if you happend to do a combined insert into table 1 with a song name, and in table 2 you inserted the lyrics, but if there were no lyrics you set the lyrics field to blank, how to delete those records in the 2nd table. For instance, if you imported the song names and lyrics from a comma seperated file where you have a Song Name, Lyrics and imported them into a MySQL database.
    intragenesis, llc professional web & graphic design

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2005
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the way it is tho is, there is no record at all for the lyrics in the table texts

    so when someone views a lyric record on www.spotsong.com and there is no texts record it shows an error...


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
  •