CODEWITHSUNDEEP
mysqlsqlinsert
Yaniv
Yaniv

Reputation: 572

Insert row if it doesn't exist in more then one table

I have two tables:
One is for active users and the other one is for inactive users. Both have full name of the user and his email address. I want to be able to insert a new user only if he doesn't exist (his email address for instance) in both tables. If he exists in the inactive table I will reactivate him by just moving the user from the inactive table to the active table.

Is there a way doing that by just trying to insert with some kind of query and catching exception if he exists, and not by checking if the user exists before inserting?

Upvotes: 0

Views: 387

Answers (2)

jurgenreza
jurgenreza

Reputation: 6086

Just because your data is huge doesn't mean you should split it in two, this will cause you a lot of headache. You will have trouble not only in CRUD operations but also in linking your table to other tables. You should definitely have one table and a boolean field to indicate if the user is active or inactive.

To deal with the large data and speed of the queries in turn, you can take other actions like partitioning.

P.s: What you are trying to do is somehow reinventing partitions.

Upvotes: 2

Vince Bowdren
Vince Bowdren

Reputation: 9218

Implementing conditional logic by catching exceptions is an inefficient design. You'd be much better off by simply checking first to see if the email address exists in either table:

IF EXISTS (SELECT 1 FROM InactiveUsers WHERE Email = @new_email)
    BEGIN
    INSERT INTO ActiveUsers ...
    DELETE FROM InactiveUsers ...
    END
IF NOT EXISTS (SELECT 1 FROM ActiveUsers WHERE Email = @new_email)
    INSERT INTO ActiveUsers...

Upvotes: 0

Related Questions