CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

Unknown column when using update and join even when the column exists

I use the following sql to update a table called userStats, and it runs per user who's userId I supply.

update userStats us
join (select
    sum(postStatus = 0) published,
    sum(postStatus = 1) inactive,
    sum(postStatus = 5) recalled,
    sum(postStatus = 6) deleted
from userData where userId = @x) d on d.userId = us.userId 
set
us.published = coalesce(d.published,0),
us.inactive = coalesce(d.inactive,0),
us.recalled = coalesce(d.recalled,0),
us.deleted = coalesce(d.deleted,0),
us.total = coalesce(d.published+d.inactive+d.recalled+d.deleted,0);

I keep running into the error Unknown column 'd.userId' in 'on clause' even when the column exists in the table. I used to use this very same thing without the error all this time.

Also, concerning the use of where, is another where needed after the set columns, or will the where inside be sufficient to update only the userId I supply. It does update only the userId I supply, but I was quite uncomfortable not using one more where after the where. Do i need another where outside like I've done before?

us.deleted = coalesce(d.deleted,0),
us.total = coalesce(d.published+d.inactive+d.recalled+d.deleted,0) where userId =@x;

Upvotes: 0

Views: 96

Answers (3)

Bohemian
Bohemian

Reputation: 425238

Because you only get one row from the inner query, you don't need a join condition!

update userStats us
join (select
    sum(postStatus = 0) published,
    sum(postStatus = 1) inactive,
    sum(postStatus = 5) recalled,
    sum(postStatus = 6) deleted
    from userData where userId = @x) d
set
us.published = coalesce(d.published,0),
us.inactive = coalesce(d.inactive,0),
us.recalled = coalesce(d.recalled,0),
us.deleted = coalesce(d.deleted,0),
us.total = coalesce(d.published+d.inactive+d.recalled+d.deleted,0)
where userid = @x

But you do need a where clause in your target table.

This query would perform pretty well too, because both the inner and outer queries use an index-friendly wher clause.

Upvotes: 1

ngrashia
ngrashia

Reputation: 9904

The table aliased as d, does not explicitly select the column userID though your userData table has the value.

So, You should probably do something like:

(select
    userID,  --> This field has to be selected to use in the join condition
    sum(postStatus = 0) published,
    sum(postStatus = 1) inactive,
    sum(postStatus = 5) recalled,
    sum(postStatus = 6) deleted
from userData where userId = @x) d on d.userId = us.userId 
                                         ^
                                         |___ You were getting error here.

Upvotes: 1

Sathish
Sathish

Reputation: 4487

change in your sub query

select
    sum(postStatus = 0) published,
    sum(postStatus = 1) inactive,
    sum(postStatus = 5) recalled,
    sum(postStatus = 6) deleted,userId 
from userData where userId = @x

Then use userid in where clause it works

Upvotes: 1

Related Questions