SQLTeam.com | Weblogs | Forums

Insert missing rows


#1

Hi! Thank you for spending the time to read this (:
I have 2 tables: user_account and email_validation.
user_account has user_id, email and email_validation_id.
email_validation has email_validation_id, user_account_id, and email.

In normal and correct way, for each user in user_account (for example [user_id="123", email="user@domain.com", email_validation_id="456"]) there is row in email_validation (for example [email_validation_id="456", user_account_id="123", email="user@domain.com"]).

But, some things got wrong and now I have users with no email validation. So, I would like to add rows manually: for each user from user_account which don't have a row in email_validation (i.e. no row with it's user_id) insert [email_validation_id=<The current max of (email_validation.email_validation_id) + 1>, user_account_id=<user_account.user_id>, email=<user_account.email>] and update the user_account.email_validation_id to <email_validation.email_validation_id>.

I have no idea how to approach this :\

Thank you for your time!


#2

Please post DDL for the tables, some sample data in the form of inserts, what you've tried and what results you are expecting..