#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0

    UPDATE and ROW_NUMBER() help...


    I have the following question to the database:
    SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY "kol" DESC) AS rownum FROM "test") AS foo
    WHERE
    rownum = 5,
    I want to change value in line 5 sorted by "kol".
    I tried:
    UPDATE test SET kol = 'jakis_tekst' FROM
    (SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY "kol" DESC) AS rownum FROM "test") AS foo
    WHERE
    rownum = 5 ) AS foo2
    But it changes all records in column "kol". Maybe somebody knows how to solve my problem?
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,938
    Rep Power
    1045
    Hi,

    take a close look at the query. What it says is this:

    sql Code:
    UPDATE
    	test
    SET
    	kol = 'jakis_tekst'
    FROM
    	...
    ;

    This is an unconditional update. The WHERE clause in the subquery doesn't help you, because it doesn't refer to the update statement.

    What you want is a conditional update:

    sql Code:
    UPDATE
    	test
    SET
    	kol = 'jakis_tekst'
    FROM
    	... AS foo
    WHERE
    	foo.kol = test.kol
    	AND foo.rownum = 5
    ;
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0
    I changed my query to:

    PHP Code:
    UPDATE test 
    SET kol 
    'text' 
    FROM 

    SELECT FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY "kol" DESC) AS rownum FROM "test") AS foo1 
    ) AS foo 
    WHERE 
    foo
    .kol test.kol 
    AND foo.rownum 

    But it still changes all records in column "kol"
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,938
    Rep Power
    1045
    Please start indenting your queries. I'm glad that you at least didn't cram everything into a single line, but it's still hard to follow the structure without any indentation.

    Originally Posted by Raxim
    But it still changes all records in column "kol"
    Not on my PC:

    sql Code:
    CREATE TABLE test (
    	kol VARCHAR(255) PRIMARY KEY
    )
    ;
    INSERT INTO test (kol) VALUES ('abc'), ('def'), ('ghi'), ('jkl');
     
    -- show row numbers
    SELECT
    	*
    	, ROW_NUMBER() OVER (ORDER BY kol DESC) AS rownum
    FROM
    	test
    ;
     
    -- change kol to 'new' in row 3 ('def')
    UPDATE
    	test
    SET
    	kol = 'new'
    FROM
    	(
    		SELECT
    			kol
    			, ROW_NUMBER() OVER (ORDER BY kol DESC) AS rownum
    		FROM
    			test
    	) AS foo
    WHERE
    	foo.kol = test.kol
    	AND foo.rownum = 3
    ;
     
    -- check result
    SELECT
    	kol
    FROM
    	test
    ;

    Note that I've sanitized your query: I've indented the lines, replaced the "death star" (*) and removed the unnecessary double subselect.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo