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

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

2 answers

0 votes

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.

0 votes
Peter Koczan Atlassian Team Nov 22, 2012

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.

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!

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.

Peter Koczan Atlassian Team Nov 25, 2012

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.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,760 views 11 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot