Updating with two selects

Question

"id"    "type"  "parent"    "country"   "votes" "perCent"
"1"     "1"     "0"         "US"        "0"     "0"
"2"     "2"     "1"         "US"        "0"     "0"//votes = 8 i.e total of id3 votes and id7votes. Both have id=2 as parent, and id=2 is type 2
"3"     "3"     "2"         "US"        "4"     "0"
"7"     "3"     "2"         "US"        "4"     "0"
"19"    "3"     "1"         "US"        "4"     "0"
"4183"  "10"    "3"         "US"        "2"     "0"
"4184"  "10"    "3"         "US"        "2"     "0"
"4185"  "10"    "7"         "US"        "2"     "0"
"4186"  "10"    "7"         "US"        "2"     "0"
"4187"  "10"    "19"        "US"        "2"     "0"
"4188"  "10"    "19"        "US"        "2"     "0"

I'm trying to update col type=2 with the sum of votes where its id is parent. I've been trying the below, but seem to be going no where since this involves 3 statements and I'm very backward with joins and multiple selects.

UPDATE  likesd a
        INNER JOIN
        (
            SELECT  parent, SUM(votes) totalVotes
            FROM    likesd
            WHERE   type = 3
            GROUP   BY parent
        ) b ON a.country = b.country
SET     a.votes = b.totalVotes
WHERE   a.id = b.parent;

It's actually like this:

select id from likesd where type = 2
select sum(votes) as totalVotes where parent = id
update likesd set votes = totalVotes where id = parent and country = country

Any idea how this can be done. I'm ok with two selects, but the third one has be stuck.

Edit: Type = 2 repeats in the table

Amit Singh · Answer 1 · 2013-05-20T05:19:38.237

1

Try like this...

  UPDATE  likesd  inner join 
(Select id, ifnull((Select Sum(Votes) from Likesd A where A.parent=B.Id),0) as suvotes from Likesd) B on B.id=likesd.Id
    Set likesd.Votes=B.suvotes
    where type=2

edited May 20 '13 at 05:19

answered May 20 '13 at 04:46

Amit Singh

8,039
20
29

/* SQL Error (1093): You can't specify target table 'likesd' for update in FROM clause */ – Norman May 20 '13 at 04:57

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

UPDATE likesd a, (SELECT Parent, SUM(Votes) as TotalVotes
                  FROM likesd
                  GROUP BY Parent) b
SET a.Votes = b.TotalVotes
WHERE a.Type = 2
    AND a.id = b.parent

Reference: You can't specify target table for update in FROM clause

edited May 23 '17 at 11:57

Community

1
1

answered May 20 '13 at 05:16

jdl

1,104
8
12

score 0 · Answer 3 · answered May 20 '13 at 04:55

0

yoU can't specify the taget table in from when select and update from same table. This cant be done without procedure. There is one way of doing this if you want.

CREATE TABLE temp SELECT * FROM likesd;
UPDATE  likesd  B
SET B.votes=(SELECT SUM(votes) FROM temp A WHERE  A.parent=B.TYPE)
WHERE TYPE=2; 
DROP TABLE temp;

Run all queries one by one in your code.

answered May 20 '13 at 04:55

Manish Jangir

505
3
9

Can it be done like the sql I posted in my question. Using join. – Norman May 20 '13 at 04:57
You can not do it using inner join or any other else. – Manish Jangir May 20 '13 at 05:06

Updating with two selects

3 Answers3