58038

How to increment column by one group wise

<h3>Question</h3>

I have a table called productLocation, and its data structure as follows, SQLFiddle

+--------------+-------------+-----------+ | FkLocationId | FkProductId | SortValue | +--------------+-------------+-----------+ | 1 | 100 | 0 | +--------------+-------------+-----------+ | 1 | 101 | 0 | +--------------+-------------+-----------+ | 1 | 102 | 0 | +--------------+-------------+-----------+ | 1 | 103 | 2 | +--------------+-------------+-----------+ | 1 | 104 | 1 | +--------------+-------------+-----------+ | 1 | 105 | 3 | +--------------+-------------+-----------+ | 2 | 100 | 0 | +--------------+-------------+-----------+ | 2 | 101 | 0 | +--------------+-------------+-----------+ | 2 | 102 | 0 | +--------------+-------------+-----------+ | 2 | 103 | 1 | +--------------+-------------+-----------+ | 2 | 104 | 3 | +--------------+-------------+-----------+ | 2 | 105 | 2 | +--------------+-------------+-----------+ | 3 | 100 | 0 | +--------------+-------------+-----------+ | 3 | 101 | 0 | +--------------+-------------+-----------+ | 3 | 102 | 0 | +--------------+-------------+-----------+ | 3 | 103 | 1 | +--------------+-------------+-----------+ | 3 | 104 | 2 | +--------------+-------------+-----------+

For each location have its own sorting order for products. But some of the products have 0 as the SortValue Now I need to write a query to update SortValue as,

If I consider one location, FkLocationId = 1

+--------------+-------------+-----------+ | FkLocationId | FkProductId | SortValue | +--------------+-------------+-----------+ | 1 | 100 | 0 | +--------------+-------------+-----------+ | 1 | 101 | 0 | +--------------+-------------+-----------+ | 1 | 102 | 0 | +--------------+-------------+-----------+ | 1 | 103 | 2 | +--------------+-------------+-----------+ | 1 | 104 | 1 | +--------------+-------------+-----------+ | 1 | 105 | 3 | +--------------+-------------+-----------+

In the above data table, you can see, FkProductId = 100,101,102 have 0 as the SortValue. I need to update its sortValue as order by FkProductId descending order. I need to update its like

+--------------+-------------+-----------+ | FkLocationId | FkProductId | SortValue | +--------------+-------------+-----------+ | 1 | 100 | 3 | +--------------+-------------+-----------+ | 1 | 101 | 2 | +--------------+-------------+-----------+ | 1 | 102 | 1 | +--------------+-------------+-----------+

And also update the previous SortValue by one by one.

Then the complete output should be,

+--------------+-------------+-----------+ | FkLocationId | FkProductId | SortValue | +--------------+-------------+-----------+ | 1 | 100 | 3 | +--------------+-------------+-----------+ | 1 | 101 | 2 | +--------------+-------------+-----------+ | 1 | 102 | 1 | +--------------+-------------+-----------+ | 1 | 103 | 5 | +--------------+-------------+-----------+ | 1 | 104 | 4 | +--------------+-------------+-----------+ | 1 | 105 | 6 | +--------------+-------------+-----------+

Is this possible to do? I'm really confused about this, please help me to solve this. Thank you

Updated:

Suppose I have another table as product. And its data structure as follows,

+-----------+-----------+ | ProdcutId | SortValue | +-----------+-----------+ | 100 | 0 | +-----------+-----------+ | 101 | 0 | +-----------+-----------+ | 107 | 0 | +-----------+-----------+ | 108 | 1 | +-----------+-----------+ | 109 | 2 | +-----------+-----------+ | 110 | 6 | +-----------+-----------+ | 111 | 5 | +-----------+-----------+ | 112 | 4 | +-----------+-----------+ | 113 | 3 | +-----------+-----------+

I also need to do same thing for this table as well, How can I do it

Expected Output:

+-----------+-----------+ | ProdcutId | SortValue | +-----------+-----------+ | 100 | 3 | +-----------+-----------+ | 101 | 2 | +-----------+-----------+ | 107 | 1 | +-----------+-----------+ | 108 | 4 | +-----------+-----------+ | 109 | 5 | +-----------+-----------+ | 110 | 9 | +-----------+-----------+ | 111 | 8 | +-----------+-----------+ | 112 | 7 | +-----------+-----------+ | 113 | 6 | +-----------+-----------+
<h3>Answer1:</h3>

Following is a sample query for you to check and use the same logic for your update process using CTE-

DEMO HERE

Important Note: Update is a risky process and you should try with your test data first.

WITH CTE AS ( SELECT *, ( SELECT COUNT(*) FROM #Temp B WHERE B.FkLocationId = A.FkLocationId AND SortValue = 0 ) Zero_Count, -- The above Zero count is a simple count of rows with 0 -- for a specific FkLocationId. This is for adding to other -- rows where there are already value in column SortValue. The logic -- is simple, the number of row with 0 in column SortValue, -- should be add to existing value where there is value not equal 0. ROW_NUMBER() OVER (PARTITION BY FkLocationId ORDER BY SortValue ASC, FkProductId DESC) RN -- ROW_NUMBER is simply creating the Number you should replace 0 by. -- As you are looking for 1 to start for the MAX FkProductId with 0, -- I applied DESC order on that column FROM #Temp A ) UPDATE A SET A.SortValue = CASE WHEN A.SortValue = 0 THEN B.RN -- RN is created in such way, so that you can directly -- Replace your value in column SortValue by RN if SortValue = 0 ELSE A.SortValue + B.Zero_Count -- IF SortValue contains already value > 0, -- You need to just increment the value with Number -- of rows is there with value 0. I have calculated that value -- by a sub query in the CTE you can check. END FROM #Temp A INNER JOIN CTE B ON A.FkLocationId = B.FkLocationId AND A.FkProductId = B.FkProductId SELECT * FROM #Temp <blockquote>

Just use your "table_name" where I have used #temp

</blockquote>
<h3>Answer2:</h3>
with cte as (select FkLocationId, FkProductId, SortValue, ROW_NUMBER() over(partition by FkLocationId order by sortvalue,FkProductId desc) new_sort_value from productLocation where FkLocationId =1 --and SortValue =0 ) update pl SET pl.SortValue = c.new_sort_value from productLocation pl inner join cte c on pl.FkLocationId = c.FkLocationId and pl.FkProductId = c.FkProductId Select * from productLocation where FkLocationId =1 order by FkProductId

The core of the approach taken by me is based row_number window function, which partitions data over fkproductid sorted by sortvalue and fkproductid in descending manner.

There are 2 queries involved, although could have been done is single, but for simplicity's sake i have used common table expression (cte)

CTE derived from query one is used as joining table in second update query.

I have left much to self exploration, remember its imperative to think in term of sets.

来源:https://stackoverflow.com/questions/62166993/how-to-increment-column-by-one-group-wise

Recommend

  • Find lines in file that contain duplicate characters
  • Laravel 4, how to apply filters on Route::controller()
  • Splitting Numpy array based on value
  • How do I know which initializer is the designated initializer?
  • The GridView 'OrdersGridView' fired event RowDeleting which wasn't handled
  • Recommended way to locate parent element in Protractor
  • How to find the index of the minimum value between two specific columns of a matrix with Rcpp?
  • UILabel does not show all my text and it also doesn't show that there is more (…)
  • Error trying to diff '[object Object]'. Only arrays and iterables are allowed. (Ionic 3)
  • add opencv in cmakelists.txt android
  • PL/Python & postgreSQL: What is the best way to return a table of many columns?
  • Login to LinkedIn using HtmlUnit and navigate to Connections Web page
  • Find indices of 2D arrays using conditional statement in Python
  • How to enable spark-history server for standalone cluster non hdfs mode
  • lazy loading return null value
  • C# WPF Child Windows inside Main Window
  • Cannot read property “length” from undefined. (line 39, file “Code”)
  • Can you manually set Tensorflow LSTM weights?
  • overriding equals and hashcode methods in java?
  • SFINAE for class member function (one compiles the other not)
  • JavaFX resize children relative to parent
  • Any VBA code to align the picture from PowerPoint?
  • Apache same orgin request blocking
  • Newtonsoft.json serializing and deserializing base/inheirited where classes are from shared projects
  • Bulk loading into PostgreSQL from a remote client
  • Opening tel: links from UIWebView
  • what do lines starting with double-slash '//' mean in a .npmrc?
  • SpringBoot don't replacen System variable {user.home} in Spring Tool Suite Version: 3.8.4.RELEA
  • Auto send email based on the time and email address in database
  • Defer unused CSS
  • How to define a custom accuracy in Keras to ignore samples with a particular gold label?
  • What does “T extends Junk” mean in a generic class in Java?
  • Add checkbox dynamically using angular 2
  • What is the difference between dynamically creating a script tag and statically embed a script tag?
  • Ajax call on Multiple selection in Select box
  • Regex not working in java 1.5
  • Using redis as an LRU cache for postgres
  • Google App Engine backend servlet not responding
  • Bad automatic Triangulation with Mayavi for coloring a surface known only by its corner