Moving and replacing entries in a MySQL database

kaplan71 · 04-19-2008, 11:40 AM

Hi there --

I am running a MySQL, version 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0, database on one of our servers. I would like to move a group of similar entries in one set of fields to another set, and subsequently replace the data that was moved from the original group of fields with another set of similar information.

Is this possible, and if so, what would be the correct syntax to use? Thanks.

marquardl · 04-19-2008, 03:43 PM

MySQL has quite esoteric possibilities like this one (just a modified OSC sample for a select statement):

Code:

select cd.categories_name, c.categories_image, cd.categories_htc_title_tag, cd.categories_htc_description from table_categories c, table_categories_description cd where c.categories_id = '999' and cd.categories_id = '777' and cd.language_id = '666';

Note the fragile mix of multiple tables and fields in a single statement.

If you really want to make all changes within a single SQL statement, then you better wade through MySQL documentation and prepare for a lot of testing. Within in PHP or C/C++ code you better break down the procedure into multiple queries - makes it easier to maintain.

No ready to use SQL statement, sorry!

KDE Programs Naming Convention

kaplan71 · 04-21-2008, 02:44 PM

Hi there --

Thanks for your reply. How about this: What command can I use to replace all instances of one data entry in a field with a new one. For example, if I want to replace all instances the tape ID 12345 in the 'local' field with that of 678910 in the same 'local' field, what command would I use? Thanks.

forrestt · 04-21-2008, 03:01 PM

Not exactly sure this is how the table you are describing looks (your use of the word field is throwing me off), but if your tape ID is all that is in the field, it should be something similar to:

Code:

update local set tapeID = '678910' where tapeID = '12345';

HTH

Forrest

chrism01 · 04-21-2008, 09:06 PM

Yeah, the core syntax is

Code:

UPDATE <tablename>
SET <columnname> = <newval>
WHERE <columname> = <oldval>

Tables are regarded as being built up of rows & columns.

Here's the MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/
see http://dev.mysql.com/doc/refman/5.0/en/update.html for the UPDATE syntax.