CODEWITHSUNDEEP
mysqlsql
yuso88
yuso88

Reputation: 17

update with sub-query error You can't specify target table

I have a database with two tables, (URL and VOTE). I need to count all the votes in VOTE and include the result in the URL table I have tried to do it as follows but it gives me error ** You can't specify target table 'url' for update in FROM clause**

UPDATE url
set up = (select (SELECT COUNT(*) FROM vote v WHERE v.id = u.id AND v.note = 1 ) as num from url u)

How could you do this?

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

I think you just want a simple subquery:

UPDATE url u
    SET up = (SELECT COUNT(*)
              FROM vote v
              WHERE v.id = u.id AND v.note = 1
             ) ;

I'm not sure why you would want to be referring to url in the subquery.

Upvotes: 1

Related Questions