1

Please could someone help me construct an SQL query for the following...

I need to update the value 'ucr' in an existing table (db_table_1) with the 'ucr' uploaded to a temporary table (dummy_table) by matching the email address up with the email address that actually exists in a different table altogether (db_table_2).

Both db_table_1 and db_table_2 are joined by id.

db_table_1

id     | blah1  | blah2  | ucr
-------+--------+--------+----------
10001  | abc    |   33   |  VALUE1
10002  | def    |   44   |  VALUE2

db_table_2

id     | blah1  | blah2  | email
-------+--------+--------+----------
10001  | tyu    |   33   |  1@1.com
10002  | fgt    |   44   |  2@2.com

dummy_table

ucr    | name  | email
-------+-------+------------
10001  | abc   |   1@1.com
10002  | def   |   2@2.com

So something like:-

Update 'ucr' in db_table_1 with 'ucr' in dummy_table for 'id' which matches 'email' in db_table_2 in dummy_table. Sorry not sure of the correct syntax when having to join two tables and cross reference another column. Apologies if not explained correctly.

  • Please add the desired final result. – McNets Dec 14 '17 at 16:31
  • My poor example actually just replaces 10001 and 10002 with 10001 and 10002 but your answer looks good, I am testing it out now. – zigojacko Dec 14 '17 at 16:41
3

I'm not sure if this solution covers your desired result:

CREATE TABLE table1 (id int, bla1 varchar(10), bla2 varchar(10), ucr varchar(10));
INSERT INTO table1 VALUES (10001, 'ABC', 33, 'VALUE1');
INSERT INTO table1 VALUES (10002, 'DEF', 44, 'VALUE2');
CREATE TABLE table2 (id int, bla1 varchar(10), bla2 varchar(10), email varchar(100));
INSERT INTO table2 VALUES (10001, 'TYU', 33, '1@1.com');
INSERT INTO table2 VALUES (10002, 'FGT', 44, '2@2.com');
CREATE TABLE dummy (ucr int, name varchar(100), email varchar(100));
INSERT INTO dummy VALUES (10001, 'ABC', '1@1.com');
INSERT INTO dummy VALUES (10002, 'DEF', '2@2.com');
UPDATE table1
JOIN   table2 
ON     table1.id = table2.id
JOIN   dummy
ON     dummy.email = table2.email
SET    table1.ucr = dummy.ucr;
SELECT *
FROM   table1;
   id | bla1 | bla2 | ucr  
----: | :--- | :--- | :----
10001 | ABC  | 33   | 10001
10002 | DEF  | 44   | 10002

dbfiddle here

| improve this answer | |
  • 1
    Excellent, your query did exactly what I was after, thanks. – zigojacko Dec 14 '17 at 17:11
  • 1
    I'm glad to help – McNets Dec 14 '17 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.