MySql how to use UPDATE with HAVING?

Question

My table1 looks like:



id  name_co  name_r  temp  sld
 1   name1     1      ...  ...
 2   name2     1      ...  ...
 3   name2     1      ...  ...
 4   name2     1      ...  ...
 5   name3     1      ...  ...
 6   name2     1      ...  ...

I need to increment name_r if there are two or more identical name_co. To be so:

     
id  name_co  name_r  temp  sld
 1   name1     1      ...  ...
 2   name2     1      ...  ...
 3   name2     2      ...  ...
 4   name2     3      ...  ...
 5   name3     1      ...  ...
 6   name2     4      ...  ...

I tried different options and I came to this:

    UPDATE table1 
        SET name_r = name_r + 1 
        WHERE (SELECT COUNT(*) 
        GROUP BY name_co 
        HAVING name_co > 1)

The query works and returns 0 rows, but I know that in some way he's wrong, but I can't figure out what. Can anyone help? (And a bit of explanation, so I better understood)

The final two columns appear to have no relevance to the question, so I'm unclear why they're included above — Strawberry, Feb 15 '17 at 23:06
I tried: `SELECT COUNT(*) AS test FROM table1 GROUP BY name_co HAVING test > 1` to combine with `UPDATE table1 SET name_r = name_r + 1`. For `UPDATE` to work with `HAVING`. I know that this query poorly drafted. — Agent Smith, Feb 15 '17 at 23:28
If you provide a sqlfiddle or rextester, I'm sure someone will supply a solution. But leave out the irrelevant columns — Strawberry, Feb 15 '17 at 23:35

user1327961 · Accepted Answer · 2017-02-15T23:03:35.540

1

--updated intended targets
UPDATE table1 
SET    name_r = name_r + 1 
WHERE  id IN 
(
    -- return those ids again (to avoid the mysql #1093 error)
    SELECT  id
    FROM
    (   -- get all the ids for those names
        SELECT  id
        FROM    table1
        WHERE   name_co IN
        (   -- get all names that have more than one id
            SELECT   name_co
            FROM     table1 
            GROUP BY name_co 
            HAVING   COUNT(id) > 1
        )
    ) a
)

edited Feb 15 '17 at 23:03

answered Feb 15 '17 at 22:13

user1327961

452
2
8

This query returns: `#1093 - You can't specify target table 'table1' for update in FROM clause` – Agent Smith Feb 15 '17 at 22:22
This one returns: `#1248 - Every derived table must have its own alias`. So I tried this: `UPDATE table1 SET name_r = name_r + 1 WHERE id IN ( SELECT id FROM ( SELECT id FROM table1 AS alia GROUP BY id, name_co HAVING COUNT(name_co) > 1 ) AS alias ) ` It works, but returns 0 rows. – Agent Smith Feb 15 '17 at 22:45
Updated answer to include alias for subquery -- can you run the inner most subquery that has the COUNT clause... does that actually return any data for your specific data set? – user1327961 Feb 15 '17 at 22:49
saw initial query was incorrect, updated the query to correct query and included comments – user1327961 Feb 15 '17 at 22:53
hm mysql says: `#1064 - You have an error in your SQL syntax` – Agent Smith Feb 15 '17 at 23:02
@AgentSmith fixed! – user1327961 Feb 15 '17 at 23:03
Anyone able to please comment on why the answer was downvoted? Thanks! – user1327961 Feb 15 '17 at 23:11
It works great! Thank you very much! Will be a lot of things to explore sampling variation request! But there are second my mistake, beside `name_co > 1`. The request updated rows name_co and `name_r`: `2,2,2,2`, instead of `1,2,3,4` – Agent Smith Feb 15 '17 at 23:31
Oh, because it's so long winded. Plus it was wrong, but maybe that's fixed now. – Strawberry Feb 15 '17 at 23:48
At this time I don't know how to build sql query, that will add i++. I'll think about it and perhaps I'll use php. – Agent Smith Feb 15 '17 at 23:59

MySql how to use UPDATE with HAVING?

1 Answers1

Linked