CODEWITHSUNDEEP
mysqlselectinsert
Toniq
Toniq

Reputation: 5006

Mysql Select row, update values and insert into another table

I have a temporary table with some rows:

$query = "CREATE TEMPORARY TABLE {$tn} (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB";

I would like to select each row in this table, nulify id, update 'type' (for example) and insert whole selected row into another table (which has the same columns as this table).

I tried this but I am getting an error near SET ( You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET )

 foreach ($ids as $id) {//this is each id in temp table

    $query = "INSERT INTO $another_table
              SELECT * FROM {$tn} WHERE id='$id'
              SET id=NULL, type='foo'";

    $result = $conn->query($query) or die(mysqli_error($conn)); 

}

Upvotes: 1

Views: 3731

Answers (4)

Oliver G. Reid
Oliver G. Reid

Reputation: 61

FYI if you want SET many or all the columns from a table with the same column names this query run on information_schema will generate the most tedious part of the code for you:

select concat('target.', column_name ,' = ','source.', column_name ,', ') as line
from `columns` 
where `columns`.table_name = 'target';

Upvotes: 0

dognose
dognose

Reputation: 20899

If both your tables have the SAME amount of columns, with the same order, and you want to avoid listing each and every column, You could just copy the entry and updated the changed value within the same transaction: 

START TRANSACTION;

INSERT INTO table2 SELECT * FROM table1 WHERE id = 57;

UPDATE table2 SET columnYouWantToChange="New Value" WHERE id = 57;

COMMIT;

But maybe you should OUTLINE, what you are trying to achieve? having 2 tables with identical columns "smells" like bad design. Maybe you'd better use the same table along with revision numbers of your data row?

Upvotes: 1

Abelgo
Abelgo

Reputation: 772

Your SQL is not valid, You have an insert statement with a set statement in it.
All you need is a basic INSERT SELECT this is also no need for the for loop.

INSERT INTO $another_table (id, type, title) SELECT id, 'foo', title FROM {$tn}

You can do the set statement as an update after inserting all rows if that is easier to get you head around.

UPDATE $another_table SET id = NULL

https://dev.mysql.com/doc/refman/8.0/en/insert-select.html

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521179

I am not a PHP guru, but this is the syntax you should be following. You may select any number of constants you wish from a table.

INSERT INTO $another_table (id, type, title)
SELECT NULL, 'foo', title
FROM {$tn}
WHERE id = '$id';

This assumes that the other destination table uses the same name for its columns as your first table. If not, then you will have to change the first line of my query.

Note that you should ideally be using a prepared statement here.

Upvotes: 2

Related Questions