Select from a table and insert to another table and deleting it from the source table

Question

I got a table named tbl_requests and tbl_friends, I know the query on how to SELECT a certain row from tbl_requests and INSERT it into tbl_friends by ID.

My problem now is how can I delete the row from tbl_requests automatically after selecting it and inserting it to tbl_friends.

Here is my query in SELECT from tbl_requests and INSERT to tbl_friends:

INSERT INTO tbl_friends 
     SELECT * 
       FROM tbl_requests 
      WHERE ID = 1

What will I add so that the row from tbl_requests will be deleted?

You could create your own stored procedure which would do both statements. I don't see any "straight sql way". — Raphaël Althaus, Aug 29 '13 at 18:43
You could just have one table, with a flag indicating whether the request has been accepted. — Gordon Linoff, Aug 29 '13 at 18:44
yea it is @Gordon Linoff. I'll use a Boolean field as my flag. thanks — James, Aug 29 '13 at 19:27

score 3 · Answer 1 · edited May 23 '17 at 11:57

3

Have a look at: MySQL Trigger - delete after update

What you want is to trigger a delete after your insert.

DELIMITER $$
CREATE TRIGGER trigger1
  AFTER INSERT
  ON  tbl_friends 
  FOR EACH ROW 
BEGIN
  DELETE FROM tbl_requests WHERE ID = new.id;
END
$$

edited May 23 '17 at 11:57

Community

1
1

answered Aug 29 '13 at 18:44

Menelaos

23,508
18
90
155

I gave it a try on my own DB and worked... if it doesn't do tell :) – Menelaos Aug 29 '13 at 18:53
meewoK, since the OP originally mentioned being a newbie (prior to editing his post), it would help to point out the possible disadvantages along with using a trigger improperly. I'm sure you could find more, but here's a list of a few. http://blog.sqlauthority.com/2007/05/24/sql-server-disadvantages-problems-of-triggers/ – Tiny Haitian Aug 29 '13 at 18:58
I see. thanks for that @Tiny Haitian. I already figured out the solution by using conditions in my php statements. But anyway, thanks to the both of you for the new ideas I just learned. – James Aug 29 '13 at 19:06
No problem. Though there's nothing wrong with triggers (and they have their place), I just wanted to make sure you knew both sides of the story. :) – Tiny Haitian Aug 29 '13 at 19:10

score 1 · Answer 2 · edited Aug 29 '13 at 20:07

1

That can't be done in traditional sql with a single statement. The whole point of transactions is to do just that. Use

begin;
insert into tbl_friends (a,b,c) select u,v,w from tbl_requests where id = :myid;
delete from tbl_requests where id = :myid;
commit;

You may also use a trigger on the tbl_friends that deletes rows from tbl_requests where a new row is inserted.

edited Aug 29 '13 at 20:07

answered Aug 29 '13 at 18:45

user2722968

13,636
2
46
67

i thought it's possible in just a single statement. well, tnx for giving me advice :) – James Aug 29 '13 at 18:53
@James: it is possible with a single statement - just not with MySQL – Aug 29 '13 at 20:08

Select from a table and insert to another table and deleting it from the source table

2 Answers2