1

I'm using insert into select to copy a row from table1 to table2, except that I need to select data from two different rows from the same table. How can this be done?

Table 1

"id" "name" "description" "path" "country" "status" "1" "Title 1" "Description 1" "US > Consumer > Home Applicances" "US" "0" "2" "Title 2" "Description 2" "US > Business > Legal Charges" "UK" "0" 

Table 2

"id" "name" "description" "path" "newId" "newPath" "country" "status" 

Current Sql

insert into table2 select null, name, description, path, country, status from table1 where id=1; 

Trying to do the two in one go

$currentId = 1; $newId = 2; // This'll update columns name, description, path, country, status insert into table2 select null, name, description, path, country, status from table1 where id=$currentId; // This'll need to update newId, newPath same row insert into table2 newId, newPath from table1 where id=$newId; //Trying to achiev insert into table2 select null, name, description, path, country, status from table1 where id=$currentId, insert into table2 //newId, newPath select id, path from table1 where id=$newId; 

1 Answer 1

3

Use JOIN

INSERT INTO table2 SELECT t1.id, t1.name, t1.description, t1.path, t2.id, t2.path, t1.country, t1.status FROM table1 t1 JOIN table1 t2 ON t1.id = $currentId AND t2.id = $newId 

Here is SQLFiddle demo

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.