CODEWITHSUNDEEP
mysql
may
may

Reputation: 755

mysql - update distinct row in one table

I have this table:

enter image description here

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

Upvotes: 0

Views: 6641

Answers (3)

Will B.
Will B.

Reputation: 18416

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.

Upvotes: 1

chirag satapara
chirag satapara

Reputation: 1937

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.

Upvotes: 1

Kaushali de silva
Kaushali de silva

Reputation: 70

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)

Upvotes: 1

Related Questions