CODEWITHSUNDEEP
mysql
Murali
Murali

Reputation: 87

Update column value by querying concatenated column values with another table value

I have two tables as below. The Id column value in both the tables mentioned below is auto-incremented.
Group
+----+-----------+----------------+
| Id | GroupId | GroupName |
+----+-----------+----------------+
| 1 | 10 | [email protected] |
| 2 | 20 | [email protected] |
| 3 | 30 | [email protected] |
| 4 | 40 | [email protected] |
+----+-----------+----------------+

Customer
+---+-----------------+------------+----------+---------------+
| Id | GroupAliasName | Domain | GroupId | CustomerName |
+---+-----------------+------------+----------+---------------+
| 1 | Grp1 | abc.com | null | Cust1 |
| 2 | Grp2 | abc.com | null | Cust2 |
| 3 | Grp3 | xyz.com | null | Cust3 |
| 4 | Grp4 | def.com | null | Cust4 |
+---+-----------------+------------+----------+---------------+

Now from Customer table 'GroupAliasName' and 'Domain' when concatenated as 'GroupAliasName@Domain' is equivalent to 'GroupName' in Group table.

Using the concatenated value from Customer table, I need to pull the 'GroupId' from the Group table and populate the same in Customer table's 'GroupId' as below

Customer
+----+----------------+------------+----------+---------------+
| Id | GroupAliasName | Domain | GroupId | CustomerName |
+----+----------------+-----------+---------+-----------------+
| 1 | Grp1 | abc.com | 10 | Cust1 |
| 2 | Grp2 | abc.com | 20 | Cust2 |
| 3 | Grp3 | xyz.com | 30 | Cust3 |
| 4 | Grp4 | def.com | 40 | Cust4 |
+----+----------------+------------+----------+---------------+

The query which I tried is as below

UPDATE Customer SET GroupId = (SELECT GroupId FROM Group G WHERE GroupName = (SELECT CONCAT(GroupAliasName, '@', Domain) AS GroupName FROM Customer WHERE Domain IS NOT NULL) AND G.GroupName = GroupName);

But I get error as 'Subquery returns more than 1 row'.
Please suggest or provide your inputs.

Upvotes: 2

Views: 64

Answers (2)

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

Try this:

update customer as cust
inner join `group` grp on  concat(cust.groupaliasname, '@', cust.domain) =  grp.groupname
set cust.groupId = grp.groupId;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

Try with somethings like this 

UPDATE Customer as c
INNER JOIN Group as g on (  CONCAT(c.GroupAliasName, '@', c.Domain) =  g.GroupName)
SET c.GroupId = g.GroupId;

Upvotes: 1

Related Questions