mysql - update distinct row in one table

Question

I have this table:

it produces by this query SELECT DISTINCT code, tariff_diff FROM mytable .

Now, I want to update tariff_diff = 1 if code appear more than 1. (as example, I want to update tariff_diff = 1 where row Kuta,DPS50xxx)

I have tried :

update mytable SET tariff_diff = 1 WHERE in(select distinct code, tariff_diff from mytable)

But i am getting error syntax. Operand should contain 1 column

have a look: https://stackoverflow.com/questions/14046355/how-do-i-delete-all-the-duplicate-records-in-a-mysql-table-without-temp-tables — janith1024, Aug 28 '17 at 03:37

score 1 · Accepted Answer · answered Aug 28 '17 at 03:54

1

If you want to alter the all the rows with same code you can use this.

UPDATE mytable SET mytable.tariff_diff = 1 WHERE mytable.code IN(SELECT count(*), code, tariff_diff from mytable GROUP BY code HAVING count(*)>1)

answered Aug 28 '17 at 03:54

Kaushali de silva

70
8

Will B. · Answer 2 · 2017-08-28T05:11:24.913

From what I understand, you're wanting to set the tariff_diff to 1 only if more than one of the rows that are prefixed with Kuta,DPS50. exist. Matching on Kuta,DPS50.06, Kuta,DPS50.07, Kuta,DPS50.08, Kuta,DPS50.09, Kuta,DPS50.10.

Assuming all of your records are formatted like: XXX,xxx.###. You can use SUBSTRING_INDEX to parse the prefixed text (Kuta,DPS50.) to use as an identifier.

Then you can use a derived JOIN to match the codes that have duplicates of the prefixed values and update the matching rows. If there are no duplicate values, no update will occur.

Example: http://sqlfiddle.com/#!9/658034/1 (I added an additional entry for Petang,DPS50.02 to demonstrate it works on other prefixed values.)

Query:

UPDATE mytable AS p
JOIN (
   SELECT SUBSTRING_INDEX(code, '.', 1) AS prefix_code
   FROM mytable
   GROUP BY prefix_code 
   HAVING COUNT(prefix_code) > 1
) AS c
ON c.prefix_code = SUBSTRING_INDEX(p.code, '.', 1)
SET p.tariff_diff = 1;

Result:

|                  code | tariff_diff |
|-----------------------|-------------|
|   Abiansemal,DPS50.02 |           0 |
|         Kuta,DPS50.06 |           1 |
|         Kuta,DPS50.07 |           1 |
|         Kuta,DPS50.08 |           1 |
|         Kuta,DPS50.09 |           1 |
|         Kuta,DPS50.10 |           1 |
| Kuta Selatan,DPS50.05 |           0 |
|  Kuta Ultara,DPS50.04 |           0 |
|       Mengwi,DPS50.01 |           0 |
|       Petang,DPS50.02 |           1 |
|       Petang,DPS50.03 |           1 |

This will also avoid the SQL Error (1093) https://dev.mysql.com/doc/refman/5.6/en/update.html

You cannot update a table and select from the same table in a subquery.

If your `code` column can contain more than one `.` (period), let me know and I will provide an alternative. — Will B., Aug 28 '17 at 04:39

score 1 · Answer 3 · answered Aug 28 '17 at 04:46

It is not possible to use same update table in select statement in subquery , you can find the reason in this link: Reason for not use same table in sub-query.

try below query:

SET @r_code = (select code from mytable GROUP BY code having count(code) > 1);

update mytable SET tariff_diff = 1 WHERE code in (@r_code);

You can find more about variable here in this link.More about Variables.

First of all store the id's into the some variable and then update those id's using in query.

mysql - update distinct row in one table

3 Answers3