Sql: Update Column With Increment Numbers Based on 2 Columns

SQL: Update Column with increment numbers based on 2 Columns

you can try below query by using row_number funtion

update A
set Priority= 10*rn 
from TableA A inner join

( select  date_time, row_number() over(partition by Date_time order by Date_time  ) as rn from TableA
 ) as B
 on A.Date_time=B.Date_time

Auto increment number column based on another 2 columns in the table

You can't put a window function (like ROW_NUMBER) in a computed column, but there's a great workaround. Create a scalar-valued function with the window function inside it, and then you can use the scalar function in the computed column.

CREATE FUNCTION dbo.fnGetInvoiceNo(@InvoiceID INT)
RETURNS int
AS
BEGIN
    DECLARE @InvoiceNo INT
    ;WITH cte AS 
    (
        SELECT InvoiceID, ROW_NUMBER() OVER (PARTITION BY OrgID, CompanyID ORDER BY InvoiceID) rn
        FROM dbo.Invoices
    )
    SELECT @InvoiceNo = rn
    FROM cte
    WHERE cte.InvoiceID = @InvoiceID

    RETURN @InvoiceNo
END
GO

ALTER TABLE dbo.Invoices ADD InvoiceNo AS dbo.fnGetInvoiceNo(InvoiceID)
GO

Then:

SELECT * FROM dbo.Invoices

Returns:

InvoiceID   OrgID   CompanyID   InvoiceNo
1           1       101         1
2           1       101         2
3           1       101         3
4           2       201         1
5           2       201         2
6           2       201         3
7           2       202         1
8           2       202         2
9           2       202         3

Update/Increment multiple MySQL columns in one query

Check if the datatype for cost_to is int or not.Also update the column if it's value is not null.

UPDATE `contacts` 
       SET `calls_to` = `calls_to`+1, 
             `cost_to` = `cost_to`+0.25 
  WHERE `contact_no`='0412345678' AND
          calls_to is not null AND
          cost_to is not null;

Update int column in table with unique incrementing values

declare @i int  = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices)


update prices
set interfaceID  = @i , @i = @i + 1
where interfaceID is null

should do the work

Update and Increment multiple columns on duplicate

Since the columns are not nullable you must pass 0s instead of nulls for the columns that you don't want to supply values in the INSERT statements and use comparisons to 0 instead of null:

INSERT INTO stock (price, stock1, stock2) VALUES
  ('99', '10', 0), 
  ('120', 0, '12')
ON DUPLICATE KEY UPDATE 
  `stock1-C` = `stock1-C` + (VALUES(stock1) <> 0 AND stock1 <> 0),
  stock1 = stock1 + VALUES(stock1), 
  `stock2-C` = `stock2-C` + (VALUES(stock2) <> 0 AND stock2 <> 0),
  stock2 = stock2 + VALUES(stock2)

Result:

SELECT 
    userid, 
    points, 
    RANK() OVER (ORDER BY points DESC) AS position

UPDATE a
SET a.position = b.position
FROM {table_name} a
INNER JOIN
(
    SELECT 
        userid, 
        RANK() OVER (ORDER BY points DESC) AS position
        FROM {table_name}
) b
ON a.userid = b.userid

update t
join (
    SELECT 
    Vid,
    @r:= CASE WHEN Video_id = @g THEN @r+1  ELSE @r:=1 END a_id 
    ,@g:=Video_id
    FROM t,(SELECT @r:=0,@g:=0) t1
    ORDER BY Video_id
) t1
on(t.Vid =t1.Vid)
set t.a_id = t1.a_id