spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

How do i structure a query where I am INSERTing into a new table and need to increment a column every time?


I would do this using ROW_NUMBER and a view, rather than storing the value:

CREATE VIEW dbo.SOLCategoriesSort 
AS
    SELECT  CategoryId,
            ParentId,
            Sort
    FROM    (   SELECT  CategoryId, 
                        ParentId,
                        NumberOfItems = COUNT(*)
OVER(PARTITION BY ParentId),
                        Sort = ROW_NUMBER()
OVER(PARTITION BY ParentId ORDER BY CategoryID)
                FROM    SOLCategories
                WHERE   CategoryId > 0 
                AND     ParentId > 0 
            ) AS t
    WHERE   t.NumberOfItems > 1;
GO

This will mean that you don't need to recalculate the Sort column for every insert/update/delete on the base table.

EDIT

The expected output does not seem to match up with your attempt, so I am not sure which is correct, in the attempt you have the following:

AND  ParentId IN (
    Select ParentId
    From SOLCategories
    Group by ParentId
    Having COUNT(CategoryId) > 1
)

Which implies you only want to apply a sort to categories where the parent has more than one child, however in your sample data you include:

Category ID    ParentID    Sort
1807           2546        1

And 2546 is the parent for only one category. If all categories should be given a sort, then you can simplify the view:

CREATE VIEW dbo.SOLCategoriesSort 
AS
    SELECT  CategoryId, 
            ParentId,
            Sort = ROW_NUMBER() OVER(PARTITION BY
ParentId ORDER BY CategoryID)
    FROM    SOLCategories
    WHERE   CategoryId > 0 
    AND     ParentId > 0;
GO

EDIT 2

Since you want to store the sort order so it can be manipulated, I don't think you need a separate table for this, you can simply add a column to dbo.SOLCategories. The approach I usually use for scenarios like this is a nullable column to store the sort order:

ALTER TABLE dbo.SOLCategories ADD Sort
INT NULL;

The column Sort can be modified, but is nullable so that it doesn't require triggers, or other actions to maintain. I would still use a view to maintain this, but I would add the new Sort Column to manage the ordering in the row_number function:

Sort = ROW_NUMBER() OVER(PARTITION BY
ParentId ORDER BY -Sort DESC, CategoryID)

I have used -Sort DESC to ensure that actual values are sorted before NULL, and also that 1 is given precendence over 2.

So taking the following example:

Category ID    ParentID    Sort
2005           3637        NULL
2008           3637        NULL
2009           3637        NULL

The view gives:

Category ID    ParentID    Sort
2005           3637        1
2008           3637        2
2009           3637        3

Supposing you then want 2009 to appear first, you can set this Sort in this to 1:

UPDATE  dbo.SOLCategories
SET     Sort = 1
WHERE   CategoryID = 2009;

So your data is now:

Category ID    ParentID    Sort
2005           3637        NULL
2008           3637        NULL
2009           3637        1

Which changes the view ordering to:

Category ID    ParentID    Sort
2005           3637        2
2008           3637        3
2009           3637        1

Example on SQL Fiddle


Categories : SQL

Related to : How do i structure a query where I am INSERTing into a new table and need to increment a column every time?
How to split one column into two columns just by counting the charachters and inserting them into another table via a SQL query?
left and substring functions can be used to split the column and insert as two columns in to another table insert into table2 select left(column1,2), substring(column1, 3, len(column1) ) from table1

Categories : SQL
How can I import data from .csv to a table which has auto increment column?
The trick is to use a sequence instead of an identity here. The docs describes the differences between sequence, auto_increment and identity. What is of interest to you, is that you can indeed force a value of a column with a sequence. Of course, be careful yourself to not have duplicate and to set the next value of the sequence afterwards to make sure you will avoid future duplicates. The simpl

Categories : Database
Map class to tables with different table names but same column structure
With EF Code First you can map the entity to different tables dynamically: public class OrderContext : DbContext { private readonly string _tableName; public OrderContext(string tableName) : base("name=OrderContext") { _tableName = tableName; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Order>(

Categories : C#
Inserting multiple records into a webSQL table using single query, data from array of objects
As Cl told you can loop the insert SQL command into one transaction as follows. var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024); db.transaction(function (tx) { tx.executeSql('CREATE TABLE user (id INTEGER PRIMARY KEY AUTOINCREMENT,name)'); }); var userArray = [{"name": "bob"}, { "name": "lee"}]; db.transaction(function (tx) { for (var i = 0; i <userArray.

Categories : Javascript
SQL Query getting a list of items from table based on other table column value?
You need to join one table to the other. SELECT c.ID, c.Name, c.Color FROM CAR c JOIN BLUEPRINT b ON b.CarID = c.ID WHERE b.BlueprintID = [YOURBLUEPRINTID] I have given both tables an alias, it makes it easier especially when you have lots of fields. If a field with the same name appears in both tables and you don't specify which table by using something like c.Name or Car.Name` you will get an

Categories : SQL
Recently Add
Use REGEXP_REPLACE to replace variable number of characters with same number of constant characters
How to subtract a data with nano-second in a same group
listagg alternative in Oracle
How to join 3 tables to a single table in SQL
Postgresql: Trying to Understand the Information Schema Table Constraints Info for a NOT NULL Contstraint
Data Frame in R use like SQL, possibly using sqldf()
How can I group by a table by the date?
Query slows down in Conditional IF
Getting the daily sales report given the date
Retrieve rows with unique values in a column
case sensitive sql search in vb.net
Oracle multi dimensional query
Update column when datediff is greater then other column in the same table
add value from different tables and insert total value into another table
How to prevent transaction locking in sql server for all the connections?
Check the query efficiency
how can I run this select statement inside a select statement without any problems?
Add amount once on duplicate entries
SQL Server 2012 - Unique Priority Column
Query: employees who do not have the same name
Trigger to not allow a member to rent if unpaid balance exceeds $50
Anyway way to change this Oracle SQL statment to use regs?
If you set a field name as an Alias, can you utilize that name within SQL code?
Increment next month with dayOfTheMonth SQL sybase
Update Table Set From is not working as expected
How do I find one matching strings in two txt files
SQL help needed (oracle application express)
How to Roll Up Weekending Data in SQL
How do you call a variable (that is a list of values) in a conditional statement?
Oracle INSTR backward in Oracle SQL
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.