3
My case is this::
I have two tables TAB1 and TAB2; the two tables have the same structure: (id, name, surname).
I would like to replace the content of TAB1 to the TAB2. Everything that exists in TAB2 shall be deleted and replaced by the contents of TAB1.
I would also like to do this only in a request to the database. How can I do that?
3
truncate table tab2;
insert into tab2 (SELECT * FROM tab1);
All in one instruction ? I would like to do this in one request !
@Pkandré Voce can do this in a single request. As long as you batch the commands.
@Brunocosta could if possible illustrate with an example ?
@Pkandré No rdbms the commands are always executed in batch (if I am not mistaken). Therefore the problem only exists when Voce accesses your database in the application. I can only explain if you edit your question with the database system in question and the language you are using.
@Brunocosta am using php !
@Planwebinterativa, the use of TRUNCATE TABLE seems to me better than DELETE, because it does not generate log. Of course it can only be used if there is no Trigger procedure associated with DELETE in the TAB2 table and there are no other tables pointing (foreign key) to TAB2 (if the engine is Innodb);
3
It is unclear if you only want to know the SQL commands or if you want to create the connection with a PHP script.
Therefore, in a general way,
First delete existing data:
DELETE FROM TAB2;
Then run INSERT INTO TAB2 (SELECT * FROM TAB1);
You can do this using a Mysql front-end like Phpmyadmin.
*illustrative image
You cannot do your queries at the same time as Mysql prevents multiple queries by default. It is possible to change this pattern but implies security issues.
Important to note that if you are only going to replace the data from one table with the data from another table you could just apply REPLACE INTO, without using the truncate or of delete.
REPLACE INTO TAB2 (SELECT * FROM TAB1)
That’s all it takes.
But for a specific case where you want to clean "traces" from the table TAB2 that do not exist in TAB1, it is even better to exclude everything before proceeding with the INSERT.
from what I understand and from the conversation I had with AP he really wants to do a script php to do this. It may be that he clarifies better.
@Brunocosta I’m making requets with PHP ! However I don’t see how to delete and then Insert au same time.
You cannot do your queries at the same time as Mysql prevents multiple queries by default. It is possible to change this pattern but implies security issues. Leave it as it is and run it one at a time. You do not need 2 requests. A single request suffices (get, post).
@Danielomine what he wants is to execute the queries in batch not "at the same time". That is, according to the question says apenas numa requisição ao banco de dados
I noticed this Bruno, but I found it ambiguous when he says "request php".
Browser other questions tagged php sql phpmyadmin
You are not signed in. Login or sign up in order to post.
I don’t know anything about
phpso I’ll let someone else help– Bruno Costa
Pk André. If you enter the code you have right now your question will be more accepted by the community.
– Bruno Costa