Professional Web Applications Themes

Deletion based on the result of a 3 table right joins select query (MySQL 3.23) - MySQL

I am using MySQL 3.23 I have a relatively complex database with a number of Many to Many relationships (using link tables). I want to delete all items in the NewsItems table that would be returned by this select query: SELECT NewsItems.Id FROM FeedItemLink RIGHT JOIN (MemberItemSaveFile RIGHT JOIN NewsItems ON MemberItemSaveFile.ItemID = NewsItems.Id) ON FeedItemLink.ItemID = NewsItems.Id where MemberItemSaveFile.ItemID is null and FeedID=54 Any thoughts? (sorry if this is really easy - I'm relatively new to it all). Steve...

  1. #1

    Default Deletion based on the result of a 3 table right joins select query (MySQL 3.23)

    I am using MySQL 3.23

    I have a relatively complex database with a number of Many to Many
    relationships (using link tables).

    I want to delete all items in the NewsItems table that would be
    returned by this select query:

    SELECT NewsItems.Id
    FROM FeedItemLink RIGHT JOIN (MemberItemSaveFile RIGHT JOIN NewsItems
    ON MemberItemSaveFile.ItemID = NewsItems.Id) ON FeedItemLink.ItemID =
    NewsItems.Id
    where MemberItemSaveFile.ItemID is null and FeedID=54

    Any thoughts?

    (sorry if this is really easy - I'm relatively new to it all).

    Steve

    Steve Guest

  2. #2

    Default Re: Deletion based on the result of a 3 table right joins selectquery (MySQL 3.23)

    Steve wrote:
    > I am using MySQL 3.23
    >
    > I want to delete all items in the NewsItems table that would be
    > returned by this select query:
    Since you're using MySQL 3.23, you don't have access to multi-table
    delete syntax. That was introduced in MySQL 4.0.

    If you can't upgrde, I'd recommend saving the output of the query, and
    then use it as a list of id values in an "IN" predicate.

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: Deletion based on the result of a 3 table right joins select query (MySQL 3.23)

    Bill Karwin wrote:
    > Since you're using MySQL 3.23, you don't have access to multi-table
    > delete syntax. That was introduced in MySQL 4.0.
    >
    > If you can't upgrde, I'd recommend saving the output of the query, and
    > then use it as a list of id values in an "IN" predicate.

    Thanks Bill. I do have full server access so could upgrade - but I'm
    terrified of ing my server up.

    I recently had to rebuild the server because of some autmatic update
    YUM on the server control panel.

    I have two questions:

    1 Is upgrading relatively painless

    2 Will all my existing queries work?

    Cheers.

    Steve

    Steve Guest

  4. #4

    Default Re: Deletion based on the result of a 3 table right joins select query (MySQL 3.23)

    Peter H. Coffin wrote:
    > Mostly it is, if you don't have a huge number of client applications to
    > be rebound to new libraries. While it is *possible* to make most pre 4.1
    > applications work with post 4.1 servers, the means of doing so is
    > somewhat brittle (Basically, the password hash changed and while you can
    > force old sytle passwords into the authentication you have to be Very
    > Aware of the new/old difference forever until you get the clients
    > updated.)
    >
    > The other usual issue is the routine "use mysqldump to make your backup,
    > don't just restore files". Basically, you're not upgrading, you're
    > removing the old server and putting a new one into place, and loading
    > the data.

    Thanks. I may have a go at the weekend. My log in scripts do use the
    MySQL PASSWORD() function though. I'll have a think about it.

    I DO need to do some complex-ish queries (sub queries etc) and not
    being able to use IN is a bit of an irritation. So I really should
    upgrade.

    Would you advise going the whole hog from 3.23 to 5?

    Thanks for your tips.

    Steve

    Steve Guest

  5. #5

    Default Re: Deletion based on the result of a 3 table right joins selectquery (MySQL 3.23)

    Steve wrote:
    > Thanks. I may have a go at the weekend. My log in scripts do use the
    > MySQL PASSWORD() function though. I'll have a think about it.
    Note in the docs for the PASSWORD() function, they state that this
    function should be used only by the MySQL internals, and they reserve
    the right to change its implementation. You should use a standard
    hashing function instead.

    MD5() and SHA1() used to be recommended, but recently these functions
    have been shown to be breakable. Now SHA-256 is the hashing function
    recommended by NIST, but MySQL doesn't have an implementation built-in
    yet. MySQL AB is prioritizing this. See
    [url]http://bugs.mysql.com/bug.php?id=13174[/url]
    > Would you advise going the whole hog from 3.23 to 5?
    There's one notable place I can think of where queries may break when
    upgrading from 3 to 5. That's the case in which you mix "comma-style"
    joins with "SQL-92 JOIN styl" joins. For example
    SELECT ...
    FROM t1, t2, LEFT JOIN t3 ON ...
    See [url]http://dev.mysql.com/doc/refman/5.0/en/join.html[/url] for this.

    I agree with Peter, back up your databases into a portable format using
    mysqldump. Then restore them after the software upgrade. There have
    been some bugs reported, related to using old databases as-is after an
    upgrade to 5.0.

    Peter also covered the password issue, which is doented in these two
    pages:
    [url]http://dev.mysql.com/doc/refman/5.0/en/old-client.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html[/url]

    You should also read other upgrading issues before doing an upgrade:
    [url]http://dev.mysql.com/doc/refman/4.1/en/upgrade.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/upgrade.html[/url]

    It is recommended by some people to upgrade one release at a time. That
    is, 3.23 to 4.0, 4.0 to 4.1, 4.1 to 5.0. I'm not sure I agree that this
    is necessary; I'd recommend going straight to 5.0, but make sure to
    restore the data from a mysqldump backup.

    Also read about the "mysql_upgrade" program, included in MySQL 5.0.
    [url]http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. Wierd query result on MySQL 5 system
    By Paul Lautman in forum MySQL
    Replies: 1
    Last Post: May 4th, 07:43 PM
  2. Tyring to dictate cell color in table based on query.
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 7
    Last Post: June 6th, 09:50 PM
  3. Mysql select query with php
    By Benjamin Dickgießer in forum PHP Development
    Replies: 7
    Last Post: November 20th, 10:57 PM
  4. PHP and MySQL Table Joins
    By Ralph Freshour in forum PHP Development
    Replies: 3
    Last Post: September 1st, 09:54 PM
  5. Result of Mysql Query in a PHP table
    By Felix in forum PHP Development
    Replies: 2
    Last Post: July 8th, 01:31 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139