CODEWITHSUNDEEP
mysqlsql
jmenezes
jmenezes

Reputation: 1926

MySql every derived table must have its own alias. Even when all have

UPDATE  likesd a
    INNER JOIN (
select id, perCent, rank
from
(
    select id, perCent, elmTotals 
    , @curRank :=   
        case 
           when @prevParent = parent then
               case 
                   when @prevCent = perCent then @curRank
                   else @curRank + 1
               end
           else 1
        end rank
    , @prevParent := parent
    , @prevCent := perCent
    from 
    (
        select child.id, child.perCent, child.parent
        from likesd parent
        inner join likesd child
           on child.parent = parent.id and child.country = parent.country
        where parent.type = 3
        order by parent.id, child.perCent desc
    ) b
    cross join (SELECT @curRank := 0, @prevParent := null, @prevCent := null) c
) d ) ON a.country = d.country and a.id = d.id
    SET a.rank = d.rank
    WHERE a.type = 10;

In the above sql, I get the error: /* SQL Error (1248): Every derived table must have its own alias */ even when all my derived tables have an alias. Everything works ok without the update wrapped around the main sql. But when I add an update I get this error.

The end result is to update the column rank with the rank from the select. It's only the update that's giving problems. There big select inside it is fine.

Heres the table below, and here's a fiddle with some data

Main Table

"id"    "type"  "parent"    "country"   "votes" "perCent" "rank"
"24"    "1"     "1"         "US"        "35"    "0"
"25"    "3"     "24"        "US"        "35"    "0"
"26"    "10"    "25"        "US"        "15"    "50.00"
"27"    "10"    "25"        "US"        "10"    "33.33"
"28"    "10"    "25"        "US"        "10"    "33.33"

"29"    "1"     "1"         "US"        "50"    "0"
"30"    "3"     "29"        "US"        "50"    "0"
"31"    "10"    "30"        "US"        "20"    "40.00"
"32"    "10"    "30"        "US"        "15"    "25.00"
"33"    "10"    "30"        "US"        "15"    "35.00"

Upvotes: 0

Views: 152

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

You are missing one alias:

cross join (SELECT @curRank := 0, @prevParent := null, @prevCent := null) c
) d ) ON a.country = d.country and a.id = d.id
     ^ Here

Upvotes: 2

Mihai
Mihai

Reputation: 26784

UPDATE  likesd a
    INNER JOIN (
select id, perCent, rank
from
(
    select id, perCent, elmTotals 
    , @curRank :=   
        case 
           when @prevParent = parent then
               case 
                   when @prevCent = perCent then @curRank
                   else @curRank + 1
               end
           else 1
        end rank
    , @prevParent := parent
    , @prevCent := perCent
    from 
    (
        select child.id, child.perCent, child.parent
        from likesd parent
        inner join likesd child
           on child.parent = parent.id and child.country = parent.country
        where parent.type = 3
        order by parent.id, child.perCent desc
    ) b
    cross join (SELECT @curRank := 0, @prevParent := null, @prevCent := null) c
) d )f ON a.country = d.country and a.id = d.id
    SET a.rank = d.rank
    WHERE a.type = 10;

Upvotes: 2

Related Questions