Enhanced query to update referenced_account in Accounts table

[Shubhamjain27]

Hi,

We have a requirement where we need to update accounts table with the referenced_accountname and referenced_accountkey based on the details from user and accounts table.

Now I have a select statement with gives referenced_accountname and referenced_accountkey:

select u.username, u.customproperty18 as Referenced_Accountname, a1.accountkey as Referenced_Accountkey, a.name, a.endpointkey, a.accountkey, e.endpointname from accounts a, users u, user_accounts ua, endpoints e, accounts a1 where ua.userkey = u.userkey and ua.accountkey = a.accountkey and a.endpointkey = e.endpointkey and a1.endpointkey = a.endpointkey and u.customproperty18=a1.name and u.customproperty18 is not null and e.endpointname like 'SAP%'

Below is the screenshot:

The basic enhanced_query to update referenced_accountname and referenced_accountkey is:

select 'fl-Sales' as accounts__REFERENCED_ACCOUNTNAME, '5403' as accounts__REFERENCED_ACCOUNTKEY, accountkey as accounts__PRIMARYKEY from accounts where accountkey = '5850'

Please confirm how the above select query can be used in the enhancedquery?

[rushikeshvartak]

Yes you can use enhanced query 

[Shubhamjain27 · Accepted solution]

The query I gave is a working query, I wanted to merge both the select statement and the enhanced query.

Below is working for me:

select t.Referenced_Accountname as accounts__REFERENCED_ACCOUNTNAME, t.Referenced_Accountkey as accounts__REFERENCED_ACCOUNTKEY, accounts.accountkey as accounts__PRIMARYKEY from accounts, (select u.username, u.customproperty18 as Referenced_Accountname, a1.accountkey as Referenced_Accountkey, a.name, a.endpointkey, a.accountkey, e.endpointname from accounts a, users u, user_accounts ua, endpoints e, accounts a1 where ua.userkey = u.userkey and ua.accountkey = a.accountkey and a.endpointkey = e.endpointkey and a1.endpointkey = a.endpointkey and u.customproperty18=a1.name and u.customproperty18 is not null and e.endpointname like 'SAP%') t where accounts.accountkey = t.accountkey

[rushikeshvartak · Accepted solution]

SELECT
'fl-Sales' AS accounts__REFERENCED_ACCOUNTNAME,
'5403' AS accounts__REFERENCED_ACCOUNTKEY,
accountkey AS accounts__PRIMARYKEY
FROM
accounts
WHERE
accountkey = '5850'

UNION
SELECT
t.Referenced_Accountname AS accounts__REFERENCED_ACCOUNTNAME,
t.Referenced_Accountkey AS accounts__REFERENCED_ACCOUNTKEY,
accounts.accountkey AS accounts__PRIMARYKEY
FROM
accounts,
(
SELECT
u.username,
u.customproperty18 AS Referenced_Accountname,
a1.accountkey AS Referenced_Accountkey,
a.name,
a.endpointkey,
a.accountkey,
e.endpointname
FROM
accounts a
INNER JOIN user_accounts ua ON ua.accountkey = a.accountkey
INNER JOIN users u ON ua.userkey = u.userkey
INNER JOIN endpoints e ON a.endpointkey = e.endpointkey
INNER JOIN accounts a1 ON a1.endpointkey = a.endpointkey
WHERE
u.customproperty18 = a1.name
AND u.customproperty18 IS NOT NULL
AND e.endpointname LIKE 'SAP%'
) t
WHERE
accounts.accountkey = t.accountkey