logo
down
shadow

Update table: Summary of previous rows without using cursor or while loop


Update table: Summary of previous rows without using cursor or while loop

By : LordNikon 05
Date : November 20 2020, 03:01 PM
hope this fix your issue using a common table expression with row_number() and a gaps and islands style solution, and another row_number()for the counting:
code :
;with cte as (
  select 
      id
    , updown
    , grp = row_number() over (order by id) - row_number() over (partition by UpDown order by id)
  from t
)
select 
    id
  , updown
  , ContinuesUp   = case when updown =  1 then row_number() over (partition by updown, grp order by id) end
  , ContinuesDown = case when updown = -1 then row_number() over (partition by updown, grp order by id) end
  , ContinuesStop = case when updown =  0 then row_number() over (partition by updown, grp order by id) end
from cte
order by id
+----+--------+-------------+---------------+---------------+
| id | updown | ContinuesUp | ContinuesDown | ContinuesStop |
+----+--------+-------------+---------------+---------------+
|  1 |      1 | 1           | NULL          | NULL          |
|  2 |      1 | 2           | NULL          | NULL          |
|  3 |      1 | 3           | NULL          | NULL          |
|  4 |     -1 | NULL        | 1             | NULL          |
|  5 |      1 | 1           | NULL          | NULL          |
|  6 |      0 | NULL        | NULL          | 1             |
|  7 |     -1 | NULL        | 1             | NULL          |
|  8 |     -1 | NULL        | 2             | NULL          |
|  9 |     -1 | NULL        | 3             | NULL          |
| 10 |     -1 | NULL        | 4             | NULL          |
| 11 |      0 | NULL        | NULL          | 1             |
| 12 |      0 | NULL        | NULL          | 2             |
| 13 |      1 | 1           | NULL          | NULL          |
| 14 |      1 | 2           | NULL          | NULL          |
| 15 |     -1 | NULL        | 1             | NULL          |
+----+--------+-------------+---------------+---------------+
;with cte as (
  select *
    , Continues = row_number() over (partition by updown, grp order by id)
  from (
    select *
      , grp = row_number() over (order by id) - row_number() over (partition by UpDown order by id)
    from t
  ) sub
)
update cte set 
    ContinuesUp   = case when updown =  1 then Continues end
  , ContinuesDown = case when updown = -1 then Continues end
  , ContinuesStop = case when updown =  0 then Continues end


Share : facebook icon twitter icon
Update column using rows of a table, without using cursor or any other iteration

Update column using rows of a table, without using cursor or any other iteration


By : Alex
Date : March 29 2020, 07:55 AM
it should still fix some issue alt text http://img248.imageshack.us/img248/1797/updatetable.png
code :
UPDATE test
SET    combined = Left(j.combined, Len(j.combined) - 1)
FROM   test t
   JOIN (SELECT a.orderid,
                (SELECT categorycode + ','
                 FROM   test b
                 WHERE  b.orderid = a.orderid
                 ORDER  BY orderid
                 FOR XML PATH('')) AS combined
         FROM   test a
         GROUP  BY orderid) AS j
     ON j.orderid = t.orderid
cursor to update a row with values from the previous and current rows

cursor to update a row with values from the previous and current rows


By : Laurence Thomas
Date : March 29 2020, 07:55 AM
I wish this help you Fellow Query Writers, , OK, Try this.
code :
CREATE TABLE MyTable (Id INT Identity, Col2 int, Col3 int)

INSERT INTO MyTable (Col2, Col3)
VALUES (2,1), (3,0), (4,0),(5,0),(6,0)

SELECT * from MyTable

WHILE (SELECT COUNT(*) FROM MyTable WHERE Col3=0) > 0
BEGIN
    UPDATE TOP (1) MyTable
    SET CoL3 = (Mytable.col2 + (select col3 from mytable t2 where (t2.id = mytable.id-1)))
    WHERE Col3 = 0
END

SELECT * from MyTable
Doesn't stop CURSOR loop when update result rows in SQL Server 2008

Doesn't stop CURSOR loop when update result rows in SQL Server 2008


By : Volodymyr Puchkov
Date : March 29 2020, 07:55 AM
wish help you to fix your issue You don't need a cursor for this anyway (generally speaking try and avoid these in TSQL except for very few cases)
code :
WITH T AS
(
SELECT *,
       ROW_NUMBER() OVER (ORDER BY DriverID) + 4999 AS NewDriverID
FROM Driver
)
UPDATE T SET DriverID = NewDriverID
Update table in specific rows based on conditions for multiple previous rows

Update table in specific rows based on conditions for multiple previous rows


By : dignesh
Date : March 29 2020, 07:55 AM
I wish did fix the issue. Postgresql allows you to use more than one column for the IN statement, so I think you can just change your query to:
code :
UPDATE test SET indicator = 0 
WHERE (day, company) IN (
SELECT day, company
    FROM (
        SELECT company, day, 
        COUNT(CASE WHEN indicator <> 0 THEN 1 END) 
            OVER (PARTITION BY company ORDER BY day 
                ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) As cnt
        FROM test
        ) alias
    WHERE cnt >= 2)
Cursor loop update Table by many values

Cursor loop update Table by many values


By : user1132256
Date : March 29 2020, 07:55 AM
I wish did fix the issue. That's because the UPDATE's WHERE clause doesn't "reference" cursor's values. Something like this:
code :
DECLARE
   CURSOR contacts
   IS
        SELECT month_id, org_unit_id,         --> include additional columns here ... 
               SUM (budget) AS budget
          FROM et_bp_gl_account a, et_bp_fact f
         WHERE     f.gl_account_id = a.gl_account_id
               AND total_flag = 0
      GROUP BY month_id, org_unit_id;
BEGIN
   FOR r IN contacts
   LOOP
      UPDATE et_bp_fact
         SET budget = r.budget
       WHERE gl_account_id IN 
         (SELECT total_element
          FROM et_bp_gl_account g, et_bp_fact f
          WHERE f.gl_account_id = g.gl_account_id
            --
            AND f.org_unit_id = r.org_unit_id   --> ... and reference them here ...
            AND g.month_id = r.month_id);
                                                --> ... or, possibly, here
   END LOOP;
END;
Related Posts Related Posts :
  • How to create table via ODBC in ansi sql compliant way?
  • Postgres. How to get all the parents that meet the childs criteria?
  • 'Exploratory' SQL queries that uses one criteria to find more criteria
  • Adding Date columns
  • Display ContactName from Guid in Gridview using SQLDataSource / Asp.Net
  • making groups based on match between column 1 and column 2 from 1 table
  • Teradata 15.10 - Remove set of characters from a string
  • PostgreSQL array_agg(INTEGER[])
  • Does a query goes through all data when you only select the last N?
  • Displaying results based on customer first order after specific date SQL Server
  • Sum records and add note what was summed up in sql
  • Need help in generating values
  • RODBC - import a table
  • SQL: How to Group By task and choose only those that are completed?
  • PostgreSQL/PostGIS Count number of visits
  • How to I get a total count?
  • Many-to-many SQL relationship
  • Procedure call inside procedure
  • How to have decreasing running total in Oracle sql
  • SQL Group function query?
  • Which is the best way to calculate Year/Month in SQL Server?
  • Best way of sanitize unparametrizable sql
  • Querying for JSON data in Oracle creates syntax error
  • Show top 1 by max column
  • SQL: Insert newly created column in the same table
  • How to extract numbers after string using regexp?
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org