Is it possible to insert values ​​from one table into another AND update the values ​​of fields in one query?

Question

I have two tables. One table is intended as a transaction history, and the other is a member details log. When the report runs, I want to move parts of the participant’s details to the transaction history, but ALSO update some field entries that would not otherwise exist.

Is it possible to select all records that meet certain criteria, insert only individual parts of the corresponding row into another table, and update other fields in one query?

For instance:

In table 2, I have the member name, date of registration, and member. I want to move the above entries to table 1, but also update the field (status) equal to "processed".

Note. I also use php and pdo to connect to mysql database.

Is this possible in a single request?

Answer 1

After much thought, I decided to use the ircmaxell tip and just run a few queries. This not only simplifies the work, but also allows me to greatly simplify the sorting.

As he said above, "Don't get trapped less, always better."

Answer 2

You did not indicate whether the rows you want to update match the ones you insert. I assume they are:

insert into table1
(member_name, date_registered, memberid, status)
select member_name, date_registered, memberid, 'processed'
from table2
where SomeField = MyCriteria

Answer 3

:

SELECT *, "processed" INTO table2 FROM table1

, , :

SELECT field1, field2, field3, "processed" INTO table2 FROM table1

It should be noted that it is assumed that you want to write to table 2, including the processed variable (can I suggest a boolean?) If you want the “Processing” in another table to become more complex.

Edit: Apparently mysql does not support selections so ...

INSERT INTO table2 SELECT field1, field2, field3, "processed" FROM table1

Redfilters code works