Can I copy something to an other table and also update this in the same table?
Like:
INSERT INTO 'table_new' (name) values ("thomas")
At the same time:
UPDATE 'table_old' set ChangesWereMadeAt = (the date, where the changes were made)
Can I put something in other table, while it stays also in the old table and just updates one column ?
I work with PHP/MySql
use LAST_INSERT_ID();
INSERT INTO 'table_new' (name) values ("thomas");
$last_id_inserted = LAST_INSERT_ID();
UPDATE 'table_old' SET blah='$blah' WHERE id='$last_id_inserted';
Begin by inserting your new line into your table.
Then delete the line from the first table.
And finally copy the line you inserted with this:
INSERT INTO first_table
SELECT *
FROM dues
WHERE id = 5;
Like: INSERT INTO 'table_new' (name) values ("thomas")
At the same time:
UPDATE 'table_old' set ChangesWereMadeAt = (the date, where the changes were made)
Based on this, I'd say the solution would be to make two requests.
This means... Can I put something in an other table while it stays also in the old table and just updates one column ?
But based on this, I could give a sort of solution.
$reqSelect= 'select * from YourTable
where [insert the condition that would make you select only the data you want to copy]';
$data = mysqli_query($connection, $reqSelect);
$reqInsert='insert into YourDestinationTable(row1, row2, etc)
values('.$data[row1].', '.$data[row2].', '.$data[etc]);
mysqli_query($connection, $reqInsert);
$reqUpdate='update YourTable where [conditions here]
set TheRowYouWantToModify = '.data[TheDataYouWantInYourRow];
mysqli_query($connection, $reqUpdate);
This sounds like it would be easier to solved my a MySql trigger. There is a basic example here: Creating Triggers to add the data into Audit Table
