CODEWITHSUNDEEP
mysqlsqlsql-update
Norman
Norman

Reputation: 6365

Where would the where clause go in this sql?

SQL Fiddle here

"id"    "type"  "parent"    "country"   "totals"
1       3       0           US          0       //Desired output 5
2       10      1           US          0
3       10      1           US          0
4       10      1           US          0
5       10      1           US          0
6       10      1           US          0

7       3       0           US          0       //Desired output 5
8       10      7           US          0
9       10      7           US          0
10      10      7           US          0
11      10      7           US          0
12      10      7           US          0

13      3       0           SE          0       //Desired output 1
14      10      13          SE          0

15      3       0           SE          0       //Desired output 3
16      10      15          SE          0
17      10      15          SE          0
18      10      15          SE          0

In the above table, I'm trying to update the parent with the count of its children (how many children a parent has. I'll supply the parent id and country).

The parents are type 3 and children are type 10 and the country codes are at the side.

What I'm trying to do is:

$parent = 10;
$country = 'US';

`select count(*) as totalChildren, parent, country where type= 10 and parent = $parent and country = $country` then
`update table set totals = totalChildren where parent = parent from above and country = country from above`.

I currently use the below SQL to update the entire table, but if i need to update only a praticular parent where should I put the where clause in the below sql. I'm a little confused with this.

UPDATE  likesd a // where parent = $parent and country = $country - where should this go?
        INNER JOIN
        (
            SELECT  country, parent, count(id) totalChildren
            FROM    likesd
            WHERE   type = 10
            GROUP   BY parent
        ) b ON a.id=b.parent and a.country = b.country
SET     a.totals = b.totalChildren
WHERE   a.type = 3 and a.id = b.parent and a.country = b.country;

Upvotes: 0

Views: 78

Answers (1)

sgeddes
sgeddes

Reputation: 62841

Since you only have a single child to parent relationship, this should work:

UPDATE likesd l
JOIN (
  SELECT COUNT(1) cnt, parent
  FROM likesd 
  WHERE parent = ?
  GROUP BY parent
  ) l2 ON l.id = l2.parent
SET l.totals = l2.cnt

Update Fiddle Demo

I'm assuming ID is your primary key, thus not needing to supply the country. If you do need to though, place it in the WHERE clause in the subquery.

Upvotes: 1

Related Questions