How can I duplicate a MySQL row?

Jonathan Greenberg
Jonathan Greenberg used Ask the Experts™
on
I'm trying to duplicate a row in a MySQL table while updating 2 fields.  Here's the MySQL:

$sql = "
	CREATE TEMPORARY TABLE links_temp SELECT * FROM links WHERE linkid=0 AND planid=8000;
	UPDATE links_temp SET linkid=NULL, schoolid=285;
	INSERT INTO links SELECT * FROM links_temp;
	DROP TEMPORARY TABLE IF EXISTS links_temp;
";

Open in new window


And here's the error message generated:

Query Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE links_temp SET linkid=NULL, schoolid=285; INSERT INTO links SELECT * FRO' at line 2

Honestly, I'm going by what I've found on the web for doing this without fully understanding the syntax. That said, how can I correct my code and achieve my goal?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Olaf DoschkeSoftware Developer

Commented:
The code works out as is when I adapt this to some test table. You might have a MySQL version problem only.

Bye. Olaf.
ste5anSenior Developer

Commented:
First of all:
How can I duplicate a MySQL row?
This is not possible in a relation. Cause duplicating the row, would mean duplicating all candidate keys, thus the primary key. Then your relying on implementation detail. Column order is normally NOT guaranteed.

Then why that construct using a temporary table?? Just specify all columns:

INSERT INTO links ( columnList )
    SELECT  columnListWithoutLinkIDAndSchoolID ,
            NULL AS linkid ,
            285 AS schoolid
    FROM    links
    WHERE   linkid = 0
        AND planid = 8000;

Open in new window

Author

Commented:
ste5an, I may have to do as you say, but my table has 64 columns, and I have about 1,200 records to duplicate and modify. So I sure would love to be able to do this without specifying each column, if possible.

Olaf, can you suggest what I might do to address the issue if it is, as you say, a "version problem"?
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

ste5anSenior Developer

Commented:
Well, it's a RDBMS. Specifying column names is part of the job.

Why does the number of rows (not records) matter? SQL is set-based.

Author

Commented:
ste5an, I'm afraid I'm not knowledgeable enough to really understand your question. I'm using "rows" and "records" synonymously - and maybe that's incorrect to do. And I don't know what it means to say that "SQL is set-based."

I have 5 records (or rows?) that I need to duplicate and modify by incrementing a single field's integer 300 times, thus creating 1,500 new records (again, I don't know if the word "records" is correct). So I'm just looking for a logical way to do this.
We need to clarify what you need by "duplicate".
From what I read in your SQL code, you want to duplicate the whole row, not including the ID key link_id

Is link_id an autoincrement fild?

I would guess that the problem is that you want to update yourself link_id.
My suggestion: read / insert all fields EXCEPT linl_id, don't even mention it. Mysql will then hadnle correctli link_id

Author

Commented:
Thanks, Bernard.

Yes, that's right: linkid is an autoincrement field. I've modified my sql as per your suggestion, but that doesn't seem to make a difference, unless I'm misunderstanding you.  

Here's the new code:

$sql = "
	CREATE TEMPORARY TABLE links_temp SELECT * FROM links WHERE linkid=0 AND planid=8000;
	UPDATE links_temp SET schoolid=285;
	INSERT INTO links SELECT * FROM links_temp;
	DROP TEMPORARY TABLE IF EXISTS links_temp;
";

Open in new window


Here's the new Error message:

Query Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE links_temp SET schoolid=285; INSERT INTO links SELECT * FROM links_temp;' at line 2

I'm grateful for any help - thanks!

Author

Commented:
Problem solved! I had been calling the sql with mysqli_query() instead of mysqli_multi_query(). I'm afraid had no idea that mysqli_multi_query() existed or was necessary.
Olaf DoschkeSoftware Developer

Commented:
Ah, OK. I tested the script in Workbench and it worked, but that's not PHP or MySQLi or PDO doing things, I just knew it works and so something must be the reason, as the message itself states, it could be the version...

check the manual that corresponds to your MySQL server version

@Ste5an, while it's true the darned * is a shortcut we shouldn't have and it all should be about full column name lists perhaps, the solution to query a record into a temp to only modify the one or two columns (including primary most likely) to create the wanted result is a way to implement a * EXCEPT (field1, field2)  or *, override  value1 as field2, value2 as field2 query. Even if one discourages such usage, it can be a way to write a query "upward compatible", even if the table structure changes, as long as those two extra fields have their current meaning.

It's more a hack than an elegant way of doing things, but one very general application I can think of is a very generic audit trail history of data, where you do copy a complete row except perhaps some rows you encrypt or replace for data privacy reasons.

Bye, Olaf.
The solution was to call the query with mysqli_multi_query() instead of  mysqli_query().

Author

Commented:
Thanks to ste5an, Bernard and Olaf.

I'm always at a loss on these rare occasions on EE when I solve my own issue. I did what appears to be the correct thing to do: click the "I answered my own question (or whatever the verbiage)" link. But I do appreciate the help offered, and if there were a way to assign the 1,000 points to those who helped, I would, as I have no use for them.

Thanks again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial