Sql-server – Update one column based on count of records of grouped value in another column

sql servert-sqlupdate

Given a table

CREATE TABLE TABLE_ITEMS (
    serial_num int null,
    item_group int null
 );

INSERT INTO TABLE_ITEMS
VALUES 
 (0,1), (1,1), (2,1), (3,1), (4,1),
 (0,2), (1,2), (2,2), (3,2), (4,2), (5,2), 
 (0,3), (1,3), (2,3);

and so on…

I need to know how to run a query which will update the rows with 0 as the serial_num to be the next number in the serial_num sequence for the given item_group

The result will look like this:

   those were the zeroes
   ↓
(**5**,1) (1,1) (2,1) (3,1) (4,1) 
(**6**,2) (1,2) (2,2) (3,2) (4,2) (5,2) 
(**3**,3) (1,3) (2,3)

I want to update an item in serial_num=0 to be the last serial_num plus 1 (if the last one was 70, it needs to be 71).

How can I do it?

Best Answer

CREATE TABLE TABLE_ITEMS (
    serial_num int null,
    item_group int null
 )
 GO

INSERT INTO TABLE_ITEMS
VALUES 
(0,1), (1,1), (2,1),(3,1) ,(4,1) ,(0,2) ,(1,2), (2,2), (3,2), (4,2), (5,2), (0,3) ,(1,3) ,(2,3)
GO

SELECT 
    Item_group, 
    ROW_NUMBER() OVER (PARTITION BY item_group order by item_group) AS AddNum 
INTO #NewSerials
from TABLE_ITEMS 
WHERE serial_num <> 0

SELECT * FROM TABLE_ITEMS

;WITH CTE AS 
(SELECT MAX(addnum) as addnum, item_group from #NewSerials group by item_group)
UPDATE ti
    SET ti.serial_num = CTE.AddNum
FROM TABLE_ITEMS ti
JOIN CTE ON CTE.item_group = ti.item_group
WHERE ti.serial_num = 0

SELECT * FROM TABLE_ITEMS

5   1
1   1
2   1
3   1
4   1
6   2
1   2
2   2
3   2
4   2
5   2
3   3
1   3
2   3

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM 
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.

Sql-server – How to add/update a column with an incremented value and reset said value based on another column in SQL

You can maintain this column easily:

;WITH x AS 
(
  SELECT case_id, modify_time, version, 
    rn = ROW_NUMBER() OVER (PARTITION BY case_id ORDER BY modify_date)
  FROM dbo.source_table
)
UPDATE x SET version = rn WHERE version <> rn;

But since you could also generate that version column on the fly every time you need to query (maybe it could be in a view), I don't see any benefit of storing the output or even having a version column in the table, since you are potentially going to be updating it more often than you read it (you will have to update entire ranges or even the entire table every time any rows are changed or added).

If you are moving data from one system to another, or one table to another, you still don't need this column to exist in the source table, and you don't need a temporary table to store this column either:

INSERT dbo.target_table(case_id, modify_time, version)
SELECT case_id, modify_time, version = ROW_NUMBER() OVER
  (PARTITION BY case_id ORDER BY modify_date)
FROM dbo.source_table;