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 0
s instead of null
s 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:
price | stock1 | stock1-C | stock2 | stock2-C |
---|---|---|---|---|
99 | 10 | 0 | 0 | 0 |
120 | 0 | 0 | 12 | 0 |
update column with incremental value
I would not use physical columns that depend on values in other rows, otherwise you have to update the entire table every time one row changes. Use a view or other mechanism to calculate the position on the fly.
The query to calculate "position" would look something like:
SELECT
userid,
points,
RANK() OVER (ORDER BY points DESC) AS position
However, if you have to make it an UPDATE
then you could use something like
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
but keep in mind that you will need to run the update every time the table is updated, so performance may be an issue if it's a decent size table that gets updated a lot.
Update value in a column based on another column in the same table in MYSQL
You can use user defined variables to give rank for each video group and then join with your real table by your auto increment column and update a_id accordingly
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
Demo
Related Topics
How to Get Column Name Based on Row Value in SQL Server
Selecting Same Column Twice from a Single Table But With Different Conditions
Oracle SQL Hours Difference Between Dates in Hh:Mm:Ss
Query to Get All Those Names of Employees,Who Have 'A' as Their Middle Character in Their Name
Constraint for Phone Number in SQL Server
Using Node.Js (Express) and MySQL to Insert for a Timestamp
How to Close Idle Connections in Postgresql Automatically
How to Select the Last Record from MySQL Table Using SQL Syntax
Mysql Convert Date from Mm-Dd-Yyyy to Yyyy-Mm-Dd Format
Removing Leading Zeroes from a Field in a SQL Statement
The MySQL Service on Local Computer Started and Then Stopped
Laravel Update Multiple Records At Once
How to Select True/False Based on Column Value
T-Sql Query to Get the String Between Two Special Characters
Sql Select Everything After a Certain Character