MySQL > 4.x & 5.x > Administration > Syntax > Deleting and Replacing Records within databases PDF Print E-mail
User Rating: / 0
PoorBest 
Written by laughzilla   
Sunday, 22 June 2008 22:08

Quite normally, you may want to delete some records from within your MySQL database(s).

Or, you may wish you replace records in one MySQL database table, with records from a similarly structured, different database table, or even within the same record.

Here's how you do those things:


# HOW TO DELETE A RANGE OF RECORDS BETWEEN CERTAIN sid NUMBERS:

Here is an example of the SQL syntax to delete a group of records within a table.
    DELETE FROM `cactus_variety_catalog` WHERE `sid` BETWEEN 101686 AND 105993 LIMIT 4307;

Note: The LIMIT 4307 says "delete this many and not more".
Note: If you had used LIMIT 1; then it would only delete the LAST RECORD in that range.
Note: LIMIT is OPTIONAL.  
 
# TO UPDATE MySQL RECORDS WITH DATA FROM ONE FIELD TO OVERWRITE THE DATA IN ANOTHER FIELD IN THE SAME RECORD:

Example: Your php file queries the db where the latitude value = 0 and then updates that field with some value using a statement similar to this:
    SELECT * FROM economy_watch where lat = '0.000000'

You could adjust your query to get stuff which is null or space and then this statement:
    UPDATE economy_watch SET lon = '$longitude' WHERE id = '$id'

You may want to write a short php file that does it for you
 
Example 1:
    SELECT * FROM economy_watch where lat = '0.000000'
    UPDATE economy_watch SET lon = '$longitude' WHERE id = '$id'

Example 2:
SELECT 'title_old' FROM `corporate_profit_records` where `title_old` IS NOT NULL AND `title_old` <> ' '
UPDATE `corporate_profit_records` SET `title` IS NOT NULL WHERE sid = '$sid'


# # # # #  LOVE YOUR ANIMALS.  # # # # #


more digital solutions found at:
MySQL.org
xdose.com
ContentAndAds.com
qmailrocks.org
FreeBSDRocks.net
TheSwamp.info
FileCorner.com

 

Next >
Last Updated ( Tuesday, 28 October 2008 21:00 )
 


Banner


Valid XHTML and CSS.