CODEWITHSUNDEEP
mysqlsql
Mike Furlender
Mike Furlender

Reputation: 4019

Convert UPDATE statement to a SELECT statement

The following statement updates a column in a table named 'test'. 

UPDATE test AS t
INNER JOIN test AS q ON(
    q.ptime = t.ptime
)
SET t.slope_Percentile =(
    (
        SELECT
            count(*)
        FROM
            (
                SELECT
                    *
                FROM
                    test
            )AS t1
        WHERE
            t1.slope < t.slope
    )* 100 /(
        SELECT
            count(*)
        FROM
            (
                SELECT
                    *
                FROM
                    test
            ) AS tz
    )
);

Instead of updating the column I would like to receive a SELECT statement that gives a readout of each row (that would be updated if it was an UPDATE statement).

More information (if needed) about the nature of the tables and the goal of the code is here: Efficient assignment of percentile/rank in MYSQL

Upvotes: 1

Views: 1907

Answers (2)

Andrew Cooper
Andrew Cooper

Reputation: 32586

How about this?

SELECT *, (
    (SELECT count(*)
    FROM test AS t1
    WHERE t1.slope < t.slope) * 100 /
    (SELECT count(*)
    FROM test as tz)) as newvalue
FROM test AS t;

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44336

SELECT t.ptime,
( 
    ( 
        SELECT 
            count(*) 
        FROM 
            ( 
                SELECT 
                    * 
                FROM 
                    test 
            )AS t1 
        WHERE 
            t1.slope < t.slope 
    )* 100 /( 
        SELECT 
            count(*) 
        FROM 
            ( 
                SELECT 
                    * 
                FROM 
                    test 
            ) AS tz 
    ) 
)  AS slope_Percentile
 FROM test AS t 
--INNER JOIN test AS q ON
--q.ptime = t.ptime

Upvotes: 2

Related Questions