How can I duplicate a MySQL row?

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!
Jonathan GreenbergAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Bye. Olaf.
0
ste5anSenior DeveloperCommented:
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

0
Jonathan GreenbergAuthor 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"?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ste5anSenior DeveloperCommented:
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.
0
Jonathan GreenbergAuthor 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.
0
Bernard S.CTOCommented:
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
0
Jonathan GreenbergAuthor 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!
0
Jonathan GreenbergAuthor 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.
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
Jonathan GreenbergAuthor Commented:
The solution was to call the query with mysqli_multi_query() instead of  mysqli_query().
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jonathan GreenbergAuthor 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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.