Need help to frame an SQL to pull out information from the database

Question

Any assistance on this is much appreciated.

I have two DB tables. Table1 is a User table with Column A (USER ID) & Column B (EMAIL ADDRESS) and Table2 is a Master User table with Column A (USER ID) & Column B (EMAIL ADDRESS). I want a SQL that can compare the Column A of Table1 with Cloumn A of Table2, if USER ID exists in both tables, Column B should be compared, if there is a mismatch, USER ID of Table1 should get updated by USER ID of Table2.

While comparing USER ID's, if USER ID of Table1 is not found in Table2, EMAIL ADDRESS should get updated by DUMMY EMAIL ADDRESS

Thanks & Regards,

Asheesh

PeterKoczan · Answer

update table1 t1 set t1.email=t2.email from table2 t2 where t2.userid=t1.userid and t2.email!=t1.email;

something like should do, altough it might not work on all types of databases.

as Nic mentioned, direct DB updates are NOT supported for Atlassian products, only in rare circumstances, thus you should avoid them unless intructed otherwise by support.

Nic Brough -Adaptavist- · Answer

Doesn't explain why you want to do this (and we'd need to know that because if you're updating users in Atlassian systems, there's a good chance you'll break stuff)

Peter's answer seems to answer the basic parts of the SQL though.

unknown · Answer

Hi Peter/Nic,

I am using Atlassian Jira 4.4.5 with Oracle 11g database. Thanks for responding back. Let me elaborate it further.

"I want to first compare USERID's between two tables, if they exist in both the tables, then EMAIL ID's should be compared, if there is a mismatch, the email id of Table2 should get updated in Table1."

If the USER ID of Table1 does not exist in Table2, then EMAIL ID of that USER ID should get updated by DUMMY EMAIL ADDRESS (this part Peter has already cleared).

Let me know if I am missing something.

Thanks!