Moving and Deleting rows in MySQL

Question

I have created two tables in MySQL which are called "birthTable" and "deathTable". When I add a person in deathTable which she/he was in birthTable, I want to delete his/her name and family from birthtable because I add him/her in deathTable. But I don't know how I can do that?

Answer 1

You can write trigger on insert on table deathTable which deletes corresponding row from birthTable.

CREATE TRIGGER trg_deathTable_insert
    BEFORE INSERT ON deathTable
    FOR EACH ROWS
BEGIN
    DELETE FROM birthTable WHERE person_id = NEW.person_id;
END;

Answer 2

This is the SQL:

DELETE FROM birthTable WHERE id = ...

where id is the name of some identifying field.

Here is the documentation for DELETE.

Answer 3

What you describe can be done manually or with triggers, but generally you should not do it.

You should have a table called people, and then simply mark them as dead or alive. Instead, you may also create two columns for their birthday and deathday, if you intend to store this information.

In general, you shouldn't be moving records simply because some attribute about them has changed.

Answer 4

What you have to do is construct a DELETE FROM-query. If you only want to delete one row from a table, you need to be certain that the parameters you give it are unique for that row. Usually this is done by having a column called id. Then, knowing the id, all you have to do is this:

DELETE FROM table WHERE id=<your id>;

I think that in your case you could just have a table called "people", and a boolean column "alive" which is 1 if the person is alive and 0 if the person is dead.

If you still want to delete one row from your birthTable, assuming you know the name of this person, you do it like this:

DELETE FROM birthTable WHERE firstName=<first name> AND lastName<last name>;

I've assumed that the column names for first and last names are firstName and lastName, you'll have to modify this to match your column names. This will delete any entry in the birthTable which matches the critera (could be more than one, if you have for example 2 people called Alan Johnson).

Hope that helps. :)

Answer 5

DELETE FROM birthTable WHERE id = xxx