Update table with data from another table under certain criteria

Question

I need to update a lot of data from one table based on criteria from another table.

My tables and records in those tables look like this:

This is Users table where i can have many records for single email, constraint is on id, email and state.

Users table

My second table Registrations where i need to execute UPDATE statement looks like this:

wrong registration data

As you can see above, i have wrong user_id. Basically i need to have id of an active user. After i execute UPDATE statement, records in this table should look like this:

expected registration result

So i need to take correct user_id with Active state from Users table and update Registrations table to replace invalid user_ids.

This is solution i came up with:

https://www.db-fiddle.com/f/uzzHwJeZNLJRnU3jvgyrSC/0

My simplified code looks like this:

CREATE TABLE USERS (
 ID VARCHAR(50) PRIMARY KEY,
 EMAIL VARCHAR(255) NOT NULL,
 STATE VARCHAR(100) NOT NULL
);
INSERT INTO USERS(ID, EMAIL, STATE) VALUES ('ACTIVE_USER_1', '[email protected]', 'ACTIVE');
INSERT INTO USERS(ID, EMAIL, STATE) VALUES ('DELETED_USER_1', '[email protected]', 'DELETED');
INSERT INTO USERS(ID, EMAIL, STATE) VALUES ('DEACTIVATED_USER_!', '[email protected]', 'DEACTIVATED');
INSERT INTO USERS(ID, EMAIL, STATE) VALUES ('ACTIVE_USER_2', '[email protected]', 'ACTIVE');
INSERT INTO USERS(ID, EMAIL, STATE) VALUES ('DELETED_USER_2', '[email protected]', 'DELETED');
INSERT INTO USERS(ID, EMAIL, STATE) VALUES ('DEACTIVATED_USER_2', '[email protected]', 'DEACTIVATED');
INSERT INTO USERS(ID, EMAIL, STATE) VALUES ('ACTIVE_USER_3', '[email protected]', 'ACTIVE');
INSERT INTO USERS(ID, EMAIL, STATE) VALUES ('DELETED_USER_3', '[email protected]', 'DELETED');
INSERT INTO USERS(ID, EMAIL, STATE) VALUES ('DEACTIVATED_USER_3', '[email protected]', 'DEACTIVATED');
CREATE TABLE REGISTRATION
(
 REGISTRATION_ID VARCHAR(50) PRIMARY KEY,
 USER_ID VARCHAR(50) NOT NULL
);
INSERT INTO REGISTRATION(REGISTRATION_ID, USER_ID) VALUES ('REGISTRATION_1', 'DELETED_USER_1');
INSERT INTO REGISTRATION(REGISTRATION_ID, USER_ID) VALUES ('REGISTRATION_2', 'DEACTIVATED_USER_2');
INSERT INTO REGISTRATION(REGISTRATION_ID, USER_ID) VALUES ('REGISTRATION_3', 'DEACTIVATED_USER_3');

And this is my UPDATE statement:

WITH active_users AS (
 select u.id as active_user_id, u.email as active_user_email from users u where u.state = 'ACTIVE'
),
inactive_users AS (
 SELECT t.user_id as inactive_user_id, uc.email as inactive_user_email FROM registration t
 join users uc on uc.id = t.user_id 
 WHERE NOT EXISTS (select u.id from users u where u.state = 'ACTIVE' and t.user_id = u.id)
)
UPDATE registration t
SET user_id = a.active_user_id
from active_users a
join inactive_users iu on iu.inactive_user_email = a.active_user_email
where t.user_id = iu.inactive_user_id;

I would appreciate any suggestion how i can improve my solution.

• Sahbaz: When i said "simplified code" i mean, i did not put unnecessary columns in those tables, i just added those that we need for this use case. So everything i wrote in question and example perfectly describes production use case.
• lemon: What's the purpose of inactive users in your UPDATE statement?

Answer 1

Too many unnecessary joins and subqueries.

Clean solution:

UPDATE registration t
SET user_id = a.id
from
 users u
 join
 users a
 on u.email = a.email
 and a.state = 'ACTIVE'
where
 t.user_id = u.id
 and u.state <> 'ACTIVE';

https://dbfiddle.uk/h_phobG8