[SQL] How to populate data into a column on a sql table where there are duplicate entries from...

Table_1 looks like this:

full_name user_id
John Smith 1234
Joe Smith 5678

Table_2 looks like this:

user user_id
Jane Doe
Jane Doe
Mark Long
Mark long

What I want to do is populate the user_id column in Table_2 based on the user_id in Table 1.

I am running this query:

UPDATE Table_2 SET user_id = (SELECT user_id FROM Table_1 WHERE Table_2.user = Table_1.user_id;

But I am getting this error message:

single-row subquery returns more than one row

I know this is because there are duplicate entries for users in Table_2. Its not an option to de-duplicate the users in Table_2. Does anyone know how I can fix this?

Continue reading...