MYSQL :: Insert A Duplicate Of An Existing Row

Duplicate Existing Database (innodb), Structure Only, Without Data
There's a database (innodb) named "test1". I want to create a new database with exact the same strcuture, but since there are many tables in this db i do not want to retype the whole commands.

I'm looking for a possibility to duplicate this database, any ideas / tools?

Insert Row Into Existing Table
I have a table with rougly 70 threads, I now need to go back and insert a couple new entries into my table, but they cant go at the end, they need to go in the middle, say like ID 40,41,and 42. How do I do this?

With INSERT Can I Increment An Existing Value In A Column?
I have a table of addresses and a seperate table with contact names -
All addresses tie to one or more names - I would like to keep track of
the number of names 'belonging' to an address and have thus included a
column in my address table called num_of_contacts. Everytime I add a
new contact, I would like to increment the num_of_contacts column in the
address table. Is this possible?

Me thinks not (or at least, my attempts so far have failed me) so I'd
appreciate it if someone could tell me if I am wasteing my time trying
and instead SELECT the record, increment it, then UPDATE it.

Replace Or Insert Duplicate
Just wondering how to write less PHP code, if MySQL does the job for me...

I have this really simple table:

CREATE TABLE `customers` (
`id` bigint(20) NOT NULL auto_increment,
`name` char(50) NOT NULL default '',
`address` char(50) NOT NULL default '',
`phone` char(9) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

Well, the point is, when I want to insert a new customer I do the following (in PHP)

- search for a customer by his name:
SELECT id FROM customers WHERE name='$name'
- if I get rows, the customer exists, so I update his data:
UPDATE customers SET name='$name',address='$address',phone='$phone' WHERE id='(the id)'
- if not, the customer doesn't exists, so I insert him:
INSERT INTO customers(name,address,phone) VALUES('$name','$address','$phone')

So... is there a way to do all of this with a simple MySQL statement like REPLACE or INSERT ON DUPLICATE KEY UPDATE ???

Insert On Duplicate Key Update
Is it possible so do something like this

"insert * from TempTabel on dublicate key update 'all fields'; "

So I don't have to write every column name. I need this, so I can use the same code on multible tabels. I could use replace, but then the foreign key in other tables are not updated.

How To Handle Duplicate Keys In Insert
I have the following table definition:

CREATE TABLE `suggested_synonyms` (
`Last_Name` varchar(255) NOT NULL,
`Synonym` varchar(255) NOT NULL,
PRIMARY KEY (`Last_Name`,`Synonym`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The table stores mapping from a last name to a synonym.
I need to insert multiple rows into the table. Something like:

insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath');
the problem is that some of the mappings may already exist in the table.
I tried to use the ON DUPLICATE KEY UPDATE syntax to prevent duplicate entries, but apparently I am not doing it correctly:

insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath') ON DUPLICATE KEY UPDATE;
As I need to specify the columns to update.

Is there a way to insert multiple rows in a single query and still be safe from duplicate rows?

Insert/Ignore/Duplicate Entry
PHP

$sql="INSERT INTO USER SET
LOGIN = '".USER_LOGIN."',
PASSWORD = '".USER_PASS."'";

$result = db->query($sql);

if ($result->isError() ) {
return false;
} else {
$retrieveID = mysql_insert_id();
}

If I leave this query as is and I already have a LOGIN NAME that is a duplicate, I get a messy error message something like this...
Notice: Query failed: Duplicate entry 'jon' or key 2 SQL: INSERT INTO ...blah blah...

MESSY!!!

So I modified the query to:

PHP

$sql="INSERT IGNORE INTO USER SET
LOGIN = '".USER_LOGIN."',
PASSWORD = '".USER_PASS."'";

$result = db->query($sql);

if ($result->isError() ) {
return false;
} else {
$retrieveID = mysql_insert_id();
}

If there is a duplicate, I dont get the messy NOTICE error, which is fine. But if I already have that entry in my USER table, it will precede to get my next available MYSQL_INSERT_ID.

I dont want that to happen if it finds a duplicate using the IGNORE. I just want it to get that duplicate records ID instead, but if it doesnt find a duplicate record, get the last INSERTED record using the MYSQL_INSERT_ID()

Insert On Duplicate Key Update Question?
I have UNIQUE comp keys with 3 columns. I want to insert new rows if the data doesn't match one of the 3 columns. If they match, don't do anything. I am trying to do it with one query without doing select first and bunch of comparsion then either insert or update depending on the comparsion.

I am think of using this:
INSERT INTO mytable ('col1', 'col2', 'col3') VALUES ('mydata1', 'mydata2', 'mydata3') ON DUPLICATE KEY UPDATE col1=col1, col2=col2, col3=col3

is it going to work? I think it first tries to insert my data as a new rows if it is not duplicated. If duplicated, it will update the data with its old data. But update will ignore the operation since it's the same data.

V4 Error On Insert With Duplicate Entries
It appears to me that MySQL version 4 returns an error messge when doing an
Insert that results in duplicate entries. Version 3 did NOT return an
error - it dropped the duplicate entries and ran to completion. Version 4
seems to STOP when it encounters a duplicate entry, so that the records
before the duplicate are inserted and the records after the duplicate are
not inserted.

3.22.27.1 - previous ver MySQL that did not return error
4.0.16.0 - current ver MySQL that returns error.
Running on Red Hat Linux 7.3

Is there a way to change this behavior to the way it was handled in version
3? Are there configuration settings on MySQL that control this?

How To Prevent INSERT On Duplicate Rows With 30 Fields
I have a MySQL table with about 30 fields. I am inserting new data, and want to be sure that there are no duplicate rows. A duplicate row would be one with ALL the 30 fields (except the auto-increment index) exactly the same. How do I do that efficiently?

Table Joins On Existing/Non-Existing Data
I have a question that involves a joining tables on potentially non-existing data, but we will know the expected values whether they exist or not.

Say we're keeping statistics on any given number of sports for attendances over weekly time periods. There's two tables to hold data.

------------------------------
TABLE: StatisticTypes
id
name
------------------------------
TABLE: Statistics
id
statisticTypeId
value
date
------------------------------

Then, say, we have three entries in the StatisticTypes table for types of sports we're keeping data on...

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

My.cnf Not Existing
this is my first posting here so plz do not flame me too much if my quest=
ion=20
marks me as a total noob.

My problem is, i play around a bit with mysql (4.0.13) and want to test t=
he=20
Replication. The Documentation at http://www.mysql.com says i have to do =
the=20
following:
Stop databases - done.
Build tarball of data and bring it on slave - done.
Modify my.cnf - ???

Here is my problem. There is no my.cnf existing on my machine. Maybe its=20
called different with the newer version? Or can it be considered empty an=
d i=20
have to build a new one? But if i have to, in which directory would i do=20
that?

ON EXISTING Clause
What I'm trying to do is update a table of mine in mySQL. However, some of the values may not exist in the table yet, while most will already be there.

I have done some searching and I think I need help. I'm assuming UPDATE won't simply act as an INSERT if the existing value is not found. From my searching it seems I need to use the ON EXISTING clause with an INSERT. However, it seems that ON EXISTING can only be used when there is a primary key. In my case, thats not the case here.

However, for me this is query confusing at the moment. Can someone provide a hand?

My table is called "rankings" and has the following structure:
comp_id - int (and an index)
uid - int
networth - int

How To Add A New Row To An Existing Table?
I am able to create database, tables, inserting values, and so forth. My question is this:

Let's say I have a table on the list of items I owned, and some time later, I need to add a new item between after row 12 and before 13. How do I do that? I did look at MySQL 5.0 Manual and didn't find anything similar to what I wanted to do.

Check For Existing Row ?
How do I determine if a row already exists ?

// see if a row has id=100

$id=100;
$query="SELECT * FROM mytable WHERE id=$id";
$result=mysql_query($query);

...what do I check now ?

Using Existing Database
I am setting up a new dev. machine and need to get an old database into the new installation of MySql. There are two databases showing on the database list 'mysql' and 'test'.
Can I drop the old database (contained in a folder) straight in with the existing databases and get any sense from it?

Existing Database
I'm using MySQL 4.1.9-nt & VC++ 6.0 .
how to get all the database names and table names?

I'm using the VC++ syntax,

CRecordset rs(&db); //here db is object of CDatabase class.
rs.Open(AFX_DB_USE_DEFAULT_TYPE,"show databases ");

Information From Existing Db
Dont know much about mySQL, so excuse my potential ignorance here :
Is it possible for a table to write information to another table automatically?
For example, if on "table1" I have a column called "code", can I write the entire contents of a specified row to another table called "abc" if the code "abc" appears in the "code" column of "table1"

Add A Record To An Existing Recordset?
I have a MySQL table with about 30 records, I use a form to insert data into the first 24. (like; Name, Phone, etc...)

The last 6 (Photo1, Photo2, Photo3, Photo4, Photo5, Photo6) are the names of uploaded images (which are uploaded from a seperate form.

How do I (Or what MySQL statement do I use) to insert the 6 names into the recordset?
(Each image name will be saved into a php variable. ($Img1, $Img2, $Img3, $Img4, $Img5, $Img6)

Using Update To Add Value To Existing Numeric Value
I have users inputting hours into a mysql database. Is there a way to have a mysql statement take my input and add it to the existing value in a record? I suppose I could just select that value and use php to add the 2 values but was thinking mysql might have a function to do this for me.

How Do I Add Default To Existing Column?
It should be easy but i've searched from mysql manual to here...

Moving Existing Fields Out
Can someone tell me what would be the best way to move fields out of an existing table into new tables?

Best Way To Get Data From Existing Database
what I'm trying to do is get data from an exhisting oracle database that is not looked after by myself but I have access to. What I want is for my MySql database to mirror the oracle one or update every 5mins or so. I have done a quick search but as I have only made simple databases before I'm not sure what to search for!

Attaching An Existing Database
I would like to attach an existing database (I have the files from another system) to MySQL running on a recovery machine - yeah, the Linux system got screwed, but I managed to recover the files.

So, having the database files (.myi, .myd and .frm), how do I go about incorporating them into the current system? (Like "attach database" in MSSQL)

Foreign Key To Existing Table?
Is it possible to add a foreign key to a table that already exists? Or do I have to drop the table and recreate it?

Accessing Existing DB From Files Off A CD
I inherited a project from another developer who was fired. There is no database documentation. There is both an SQL backup file and the original database files on a CD. I have recreated the db from the backup file and that worked perfectly. However, I don't have full confidence in the accuracy of the backup file. I was wondering if I can somehow access the db from the files on the CD.

I copied the files to a folder on my machine and then changed the MySQL data folder setting to that folder. Unfortunately MySQL wouldn't start. What would I need to do to be able to have MySQL use the files copied from the CD as the data files?

Running An Existing SQL File
I hope this question is not too 'newbie' to answer.. I'm reading a book (PHP and MySQL web development by Luke Welling and Laura Thomson), and am getting an error on an example script.

I'm trying to create tables using an existing SQL file through MySQL. According to the book, it should be done like this:

> mysql -h hostname -u username -D database -p < file.sql
(I've changed the hostname, username, etc.)

However, I get the same error over and over again: ERROR 1064 (42000): ... at line 1. The book didn't even mention where to put the file, I've tried all sorts of things, but can't get it to work..

Copy Of An Existing Table
MySQL: How I make a copy of an existing table in phpMyAdmin or SQL ?

without copy data
with copy and data

Uploading Existing Database
I just got a dedicated server and can't upload some of the larger databases. I have tried compressed (gz) as well as the none zipped and automatic. None works for a 50MB + database.

I am not sure what's wrong, maybe it times out, but then I never get the error. If that's the case then how do I change the timeout settings in MySQL. Also I am using Plesk, not sure if that matters.

Edit Existing Data
I have just started to learn MySQL and this question might be silly. I have a MySQL table with user information. I want to edit this table in a website. I need to load existing data in a HTML form and then edit the fields and update them.
I searched the net but could not find anything .

List Of Non-existing Users
I have 2 tables: tableFrom , tableTo.

I want to have the list of users that ARE in tableFrom, but NOT in tableTo.
The common field is "username".
I want all the rows from tableFrom as long as they don't already exist in tableTo.
An important point is that we use MySQL 4.0.20, so I can't use "NOT EXISTS(...)".
This query needs to be pretty fast since it might be ran many times an hour, even minutes.

Check Existing Values
In my web site I'm using PHP, & MySQL.
There its needed if a user is trying to be registerd using a existing user ID.
That is I think, it should check such a value existing and if exists it must display "Existing User ID".

Convert Existing Data
i am working on a project and i need to convert already existing data into the mysql date format. I was told for the project that the date format was necessary. my first question is what is the advantage of having the mysql date formate which goes like this yyyy-mm-dd. the format my data currently is in is like this mm/dd/yyyy. The second question is how to convert from mm/dd/yyyy to yyyy-mm-dd

Assigning Userids To Existing Table
I have a table of existing users in a database. I want to add a "userid" column to the database that automatically assigns an ID to each user when new accounts are added to the system. I know I would do something like this if I had the brains to have added the column at the time of creating the table:

ALTER TABLE writers ADD (
userid int PRIMARY KEY auto_increment
);

However, the table is now already active with users, and I want userids assigned to existing accounts on the system.

Change Existing Search Code
On my site I have this code which works fine when searching for single words or where they are adjacent words, but gives no results if 2 words are entered and are not adjacent

if ($_GET["page"]=='search')
{
$sql="SELECT C.category_description,C.category_name,A.article_title,A.article_author,A.article_snippet,A.article_category_id,A.article_id,A.article_views FROM article_master A,category_master C where A.article_category_id=C.category_id and C.category_status=1 and A.article_status=1 and (A.article_title like '%".$_GET["q"]."%' OR A.article_snippet like '%".$_GET["q"]."%' OR A.article_summary like '%".$_GET["q"]."%'OR A.article_author Like'%".$_GET["q"]."')order by A.article_title asc";
$cmdartcat = mysql_query($sql);
$metatitle="Search Results For ".$_GET["q"];

someone kindly suggested this, I have been trying to adapt the original all night but have got no where (i am not really sure what does what)

$query = "SELECT {$columns} WHERE {$ADJACENT_ONLY}";
/* Load this data into an array */
$query = "SELECT {$columns} WHERE {$ALL_WORDS_ANYWHERE}";
/* Append this data to the previously loaded */
$query = "SELECT {$columns} WHERE {$ANY_OF_THE_WORDS}";
/* Append this data to the previously loaded */

Could someone change the original code to work for ANY_OF_THE_WORDS and then hopefully I may be able to then adapt the rest. (do I have to do anything to the search box results which in this case is "q")

Import Data Via Ssh Into An Existing Table
I want to import data from several .sql files into an existing database, into one table.

I tried using the following command:
mysql -e [sqlfile] [db_name]

But the problem was when I imported the next file, it would overwrite the previous imported data.

Could anyone let me know how to do this correctly, please?

Add Auto Increment To Existing Column
I have a table with 126 rows in it. The guy that originally created the table didn't think to make the id column an auto incremented one.

For the PHP script I am writing I could really do with making the id column auto increment.

I've tried

CODEALTER TABLE products CHANGE product_id product_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT

Moving An Existing MySQL Database From
I have an existing database already created on 4.0.25-standard. It is too large to import to my new server via phpmyadmin, so I'm using mysqldump. The new server has MySQL 4.1.12 instead of 4.0.25. I am running this command:

mysqldump -u root -p starfall < smf1 --create-options --compatible=mysql40

but I get this: Code:

MySQL -- Editing Existing Data In A Row
I looked through ALTER table, and some tuts over at w3schools (my heros) but I can't seem to find a way to edit EXISTING data in an EXISTING row of a table.

Add Multiple Rows To An Existing Table?
I have a table named manufacturers, it has one row in right now. I want to upload all of my manufacturers into the table. Is there a way to add multiple rows to the table at the same time? If not, what is a quick way to get about 1,000 manufacturers uploaded into a table?

Adding To Existing Info In A Field
In Command Prompt, I want to add a number to 'address' for, say, id = 23.

Is there a way to say append or concatinate the record with this additional information? In other words, NOT to do what update seems to do, and that's remove everything else.

Append Existing Data In A Table
I am working on an existing osCommerce store. There are over 200 products, each of which needs the associated image file name changed.

Rather than do this manually (any number of ways), I am unsure if there is a query I can run to save me lots of aggravation. What I would like to do is update whatever is in the field and change it to add the same 5 characters:

The Table is Products
The Field is products_image

Examples of existing data in this field are:

abcdef.jpg
ght.jpg
fire_777.jpg
oops.gif (the replacement image is a jpg)

The result I seek is:

abcdef_th.jpg
ght_th.jpg
fire_777_th.jpg
oops._th.jpg

Logic tells me to start at the end of the data, remove the last 4 characters (.jpg) and append 5, in this case: _th.jpg Could someone please give me a hand with this? I have spent many days redoing the image files and they are nearly ready to go into a live store.

Append Data To Existing Cells
I have an existing table. I need a way to append data in one of the columns. How do I append data in cells so I do not overwrite the existing data already in the cells in the column?

The fields in my table are `Source` , `Theswords` , `Topic` , `Subtopic` , `References`

The table name is ViewNew

The column I want to append is Theswords

How To Add A Auto_increment Field To An Existing Table
I want to add a field called "id" starting from 1 and auto increment, but it seems that mysql doesn't allow me to do the following.

alter table existing_table add id INT AUTO_increment;

Message: Incorrect table definition; there can be only one auto column and it must be defined as a key. But I don't have a key or auto column in the existing_table.

Create Php Site From Existing Database
I have been contacted by one of my clients to construct a knowledge bank based on their ms access database. I have successfully converted the ms access database to MySQL using the GUI conversion tools supplied by MySQL.com . I am wondering, and I don't know whether this is the right site to do this, if I can easily create a php site based on the database tables that have been converted.

Adding A Foreign Key Into An Existing Table
May I know how should I do that?

The SQL statement should be:

CREATE TABLE USER_SYSTEM_SECURITY (
USERID VARCHAR(32),
HOSTID VARCHAR(32),
HOME_DIRECTORY VARCHAR(32) NOT NULL,
ACCESS_RIGHTS INT(3) NOT NULL,
PASSWORD_EXPIRY_DATE DATE,
ROLES VARCHAR(32) NOT NULL,
PRIMARY KEY (USERID, HOSTID),
FOREIGN KEY (USERID) REFERENCES MEMBER (USERID) ON DELETE CASCADE,
FOREIGN KEY (HOSTID) REFERENCES SYSTEM (HOSTID) ON DELETE CASCADE);

But I forgot to add in the last row during the table creation.

Adding Text To An Existing Feild Value
I am trying to add the string images/ to the start of a field that is already populated. For example in MS access I would write

UPDATE Products SET Products.Image1 = "images/" & [Image1]

to accomplish EXACTLY what I need.

But this in not proper syntax for MySQL. I have even tried to find the proper syntax by testing numerous things like this
UPDATE Products SET image2= 'images/' + image1 &
UPDATE Products SET image2= 'images/' & image1

The first results in an error the second results in a 0 [zero] being placed in the image2 field.