none
How to update data using row number ? RRS feed

  • Question

  • HI, May i know how to update data by using row number ?

    As below SQL try  script. 

    Select the table 

    SELECT ROW_NUMBER()OVER (ORDER BY INV_DATE) AS RowDate, INV_ID,INV_PLANQTY FROM OTH_INV_PLAN

    Second update in table by inv_id and row number. 

    UPDATE OTH_INV_PLAN set INV_DATE = '"&MDInvDate&"',INV_PLANQTY = '"&MDInvPlanQty&"' where INV_ID = '"&TInvID&"' and '"&RowDate&"' 


    Monday, November 26, 2012 2:49 AM

Answers

  • First, I really don't know what your && string means, but I believe that you must have a reason for that.

    In general, as window functions can only appear in the SELECT clause, you have to use the Common table expression or derived table to perform your request. as below:

    ;With CTE
    AS
    (
    SELECT ROW_NUMBER()OVER (ORDER BY INV_DATE) AS RowDate, INV_ID,INV_PLANQTY, INV_DATE
    FROM OTH_INV_PLAN
    )
    UPDATE CTE 
    set		INV_DATE = '"&MDInvDate&"'
    		,INV_PLANQTY = '"&MDInvPlanQty&"' 
    where	INV_ID = '"&TInvID&"' 
    		and RowDate = '"&RowDate&"';


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCSE: Data Platform | MCITP: BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Monday, November 26, 2012 3:02 AM
  • CTE(Common Table Expression) helps you to solve your issue

    WITH CTE as ( SELECT ROW_NUMBER()OVER (ORDER BY INV_DATE) AS RowDate,INV_DATE, INV_ID,INV_PLANQTY FROM OTH_INV_PLAN) UPDATE CTE set INV_DATE = '"&MDInvDate&"', INV_PLANQTY = '"&MDInvPlanQty&"' where INV_ID = '"&TInvID&"' AND RowDate = '"&TRowID &"'"

    -----------------------------

    --------------------------------



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 27, 2012 4:46 AM

All replies

  • Your question does not make sense. Can you please ask by posting DDL of your table, some input data and desired output?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, November 26, 2012 2:55 AM
    Moderator
  • First, I really don't know what your && string means, but I believe that you must have a reason for that.

    In general, as window functions can only appear in the SELECT clause, you have to use the Common table expression or derived table to perform your request. as below:

    ;With CTE
    AS
    (
    SELECT ROW_NUMBER()OVER (ORDER BY INV_DATE) AS RowDate, INV_ID,INV_PLANQTY, INV_DATE
    FROM OTH_INV_PLAN
    )
    UPDATE CTE 
    set		INV_DATE = '"&MDInvDate&"'
    		,INV_PLANQTY = '"&MDInvPlanQty&"' 
    where	INV_ID = '"&TInvID&"' 
    		and RowDate = '"&RowDate&"';


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCSE: Data Platform | MCITP: BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Monday, November 26, 2012 3:02 AM
  • I beleive u r updatin the same table based on rwdate values.Try this below query

    UPDATE T1
    SET INV_DATE = '"&MDInvDate&"',INV_PLANQTY = '"&MDInvPlanQty&"' 
    FROM OTH_INV_PLAN T1
    INNER JOIN (SELECT ROW_NUMBER()OVER (ORDER BY INV_DATE) AS RowDate, INV_ID,INV_PLANQTY FROM OTH_INV_PLAN) T2
    ON T1.INV_ID = T2.INV_ID
    WHERE T2.RowDate = <VALUES>


    Please have look on the comment

    Monday, November 26, 2012 5:07 AM
  • Short answer, you can't.

    You need to have a Primary Key on OTH_INV_PLAN so you can find and change the duplicate value.

    Monday, November 26, 2012 5:54 PM
    Moderator
  • I tried to make sense of what you posted. Were you trying to pass parameters? Do you understand that ROW_NUMBER is a function call?  We update  table using the key, not a function call.

    SELECT ROW_NUMBER()OVER (ORDER BY inv_date) AS row_date, inv_id, inv_plan_qty 
      FROM Oth_Inv_Plan;

    UPDATE Oth_Inv_Plan 
       SET inv_date = @in_inv_date,
           inv_plan_qty = @in_inv_plan_qty
     WHERE inv_id = @in_inv_id; 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, November 27, 2012 12:00 AM
  • HI, May i know how to  Update table using ROW_NUMBER() without column name in same table ? 

    i tried as below SQL script , Select row number the table as rowdate as column name. it had an error.  

    SELECT ROW_NUMBER()OVER (ORDER BY INV_DATE) AS RowDate, INV_ID,INV_PLANQTY FROM OTH_INV_PLAN

    UPDATE OTH_INV_PLAN set INV_DATE = '"&MDInvDate&"',INV_PLANQTY = '"&MDInvPlanQty&"' where INV_ID = '"&TInvID&"' AND RowDate = '"&TRowID &"'"

    Tuesday, November 27, 2012 2:17 AM
  • with cte
    as
    (
    SELECT ROW_NUMBER()OVER (ORDER BY INV_DATE) AS RowDate, INV_ID, INV_DATE, INV_PLANQTY FROM OTH_INV_PLAN
    )
    
    UPDATE cte 
    set INV_DATE = '"&MDInvDate&"'
    ,INV_PLANQTY = '"&MDInvPlanQty&"' 
    where INV_ID = '"&TInvID&"' 
     AND RowDate = '"&TRowID &"'"
    

    ?

    http://www.t-sql.ru

    • Proposed as answer by Johnson T A Tuesday, November 27, 2012 6:42 AM
    Tuesday, November 27, 2012 2:25 AM
  • CTE(Common Table Expression) helps you to solve your issue

    WITH CTE as ( SELECT ROW_NUMBER()OVER (ORDER BY INV_DATE) AS RowDate,INV_DATE, INV_ID,INV_PLANQTY FROM OTH_INV_PLAN) UPDATE CTE set INV_DATE = '"&MDInvDate&"', INV_PLANQTY = '"&MDInvPlanQty&"' where INV_ID = '"&TInvID&"' AND RowDate = '"&TRowID &"'"

    -----------------------------

    --------------------------------



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 27, 2012 4:46 AM
  • Hi Caulson,

    I believe this is just a duplicate of query asked in below thread and which was addressed.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c624633b-7d8d-4a86-b567-e421b3ee63a5/

    Thanks,

    Santhosh


    Please have look on the comment

    Tuesday, November 27, 2012 5:34 AM