CODEWITHSUNDEEP
mysqlsqlgroup-bysql-updateleft-join
javsmo
javsmo

Reputation: 1337

MySQL UPDATE with subquery with group by

I have the following tables on MySQL:

**Elements**
+------+------------------+------------------+
+ Id   + Name             + referenced       +
+------+------------------+------------------+
+ 1    + element1         + NULL             +
+ 2    + element2         + NULL             +
+ 3    + element3         + NULL             +
+ 4    + element4         + NULL             +
+ 5    + element5         + NULL             +
+ 6    + element6         + NULL             +
+------+------------------+------------------+

**References**
+------+------------------+------------------+
+ Id   + Name             + type             +
+------+------------------+------------------+
+ 1    + element1         + 1                +
+ 2    + element1         + 2                +
+ 3    + element3         + 1                +
+ 4    + element3         + 2                +
+ 5    + element3         + 3                +
+ 6    + element4         + 1                +
+ 7    + element5         + 2                +
+ 8    + element5         + 3                +
+------+------------------+------------------+

The "referenced" column on "Elements" table should have 0 for no reference of its name on "References" table and 1 for at least one reference of its name on "References" table.

This result could be selected with the following query:

SELECT Elements.Id, Elements.Name, (References.Id is not null) as referenced FROM
    Elements 
LEFT JOIN 
    References ON Elements.Name = References.Name 
GROUP BY Elements.Name ORDER by Elements.Id;

+------+------------------+------------------+
+ Id   + Name             + referenced       +
+------+------------------+------------------+
+ 1    + element1         + 1                +
+ 2    + element2         + 0                +
+ 3    + element3         + 1                +
+ 4    + element4         + 1                +
+ 5    + element5         + 1                +
+ 6    + element6         + 0                +
+------+------------------+------------------+

But I could't find a way to update the "Elements" table with this result.

Any ideas?

Upvotes: 1

Views: 120

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562438

UPDATE Elements e
LEFT JOIN References r ON e.OneName = r.OtherName
SET e.referenced = r.OtherName IS NOT NULL;

Upvotes: 1

Related Questions