CODEWITHSUNDEEP
mysqlsql
Cynial
Cynial

Reputation: 680

How to write SQL statement in this case?

For some reason, I'd like to change the database.

It's not possible to change these values manually one by one as there are so much records.

Please advise how to write this SQL, thanks.

From:

 id  percentage    type
  1     6        services
  1     10       testing
  3     8        services
  3     20       testing
  6     61       services
  6     90       testing

To:

id percentage    type
1     10       services
1     10       testing
3     20       services
3     20       testing
6     90       services
6     90       testing

Upvotes: 1

Views: 55

Answers (3)

John Woo
John Woo

Reputation: 263723

If you want to select only see Mahmoud Gamal's Answer

But if you want to permanently change the value, Use UPDATE

UPDATE  tableName a
        INNER JOIN
        (
            SELECT id, MAX(percentage) AS maxPercentage
            FROM tableName
            GROUP BY id
        ) b ON a.ID = b.ID
SET a.percentage = b.maxPercentage;

Upvotes: 3

RelicScoth
RelicScoth

Reputation: 697

Write a reference table to match the scales between your services and testing values (see example below) and then write an update query based on each row of your reference table:

id  service_val  testing_val
1       6        10
2       8        20
3       61       90

Running a select all on this table and iterating through the results in something like php, python, etc. would allow you to dynamically replace the values in your mysql query. This approach allows for the two values to not have a direct correllation as opposed to other answers (ie: service doesn't have to be a percentage of testing)

Upvotes: -1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

With a correlated query, you can do it like so:

SELECT 
  t1.id,
  (SELECT MAX(t2.percentage) 
   FROM table1 t2 
   WHERE t2.id = t1.id) percentage,
  type
FROM Table1 t1

SQL Fiddle Demo

Upvotes: 3

Related Questions