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

[Deleted user]

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]

Indeed seems like a general SQL related question, but just to play with the thought:

"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."

I see some logical problem with this:

If USER ID exists in both tables, column B shall be compared (which is the email address) and IF there is a mismatch, then update the userid from table 2 to table1, but since we compared originally by the userid, this does not really make any sense.

The second sentence seems easy though:

update table1 set email='<dummy email address>' where userid in ((select userid from table1) minus (select userid from table2));

Ugly, yet simple to understand.

If you clarify the first part of the request, I'd be happy to help.

[Deleted user]

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!

[Nic Brough -Adaptavist-]

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.

[PeterKoczan]

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-]

Could you tell us which Atlassian system you are using, and in general, what you are trying to do and why?

You've flagged "jira ondemand", which is pretty useless because a) it uses crowd and b) you won't be able to run the SQL because it's not your system.

You also don't say why there's two sets of data, it would help if you explained that.