Updating Column So That It Contains the Row Position

Updating column so that it contains the row position

This should work

update 
content,
(
  select 
  @row_number:=ifnull(@row_number, 0)+1 as new_position,
  ContentID 
  from content
  where CategoryID=1
  order by position
) as table_position
set position=table_position.new_position
where table_position.ContentID=content.ContentID;

But I would prefer to apply this first, to unset user defined variable

set @row_number:=0;

Added by Mchl:

You can do that in one statement like this

update 
content,
(
  select 
  @row_number:=ifnull(@row_number, 0)+1 as new_position,
  ContentID 
  from content
  where CategoryID=1
  order by position
) as table_position,
(
  select @row_number:=0
) as rowNumberInit
set position=table_position.new_position
where table_position.ContentID=content.ContentID;

How to update a entire column using row index and hardcode value in Mysql?

Maybe this is what you're aiming to do:

WITH C AS
(
  SELECT email,ROW_NUMBER() OVER(ORDER BY id ASC) AS rowid
  FROM cus       
)
UPDATE cus 
 JOIN C
 ON cus.email=C.email
 SET cus.email=CONCAT(rowid,'email@gmail.com');

Join the table you want to update (cus) with cte of C then do the update accordingly.

Here's a demo

@QisM have raised a concern over this syntax when the email is not unique and since OP didn't mention, I agree that this is not the solution if the email indeed is not unique. Due to that, I've made a slight modification to the syntax:

WITH C AS
(
  SELECT id, email, ROW_NUMBER() OVER(ORDER BY id ASC) AS rowid
  FROM cus       
)
UPDATE cus 
 JOIN C
 ON cus.id=C.id AND cus.email=C.email
 SET cus.email=CONCAT(rowid,'email@gmail.com');

Now the cte is with id and the JOIN C ON .. condition I've added the checking of matching id. Upon testing, this should fix the issue if e-mail is not unique.

How to update each row of a column from one table with a list of values from another?

Given the following table structure:

CREATE TABLE #tempA (stringEntry NVARCHAR(50));
INSERT INTO #tempA (stringEntry) VALUES ('abcd'), ('efgh'), ('ijkl');

CREATE TABLE #tempB (stringEntry NVARCHAR(50));
INSERT INTO #tempB (stringEntry) VALUES ('mnop'), ('qrst'), ('uvwx');

You can do the following:

SELECT
  ROW_NUMBER() OVER(ORDER BY #tempA.stringEntry) AS RowNumber,
  #tempA.stringEntry AS entryA
INTO #tempA2
FROM #tempA;

SELECT
  ROW_NUMBER() OVER(ORDER BY #tempB.stringEntry) AS RowNumber,
  #tempB.stringEntry AS entryB
INTO #tempB2
FROM #tempB;

UPDATE #tempA 
SET #tempA.stringEntry = #tempB2.entryB
FROM #tempA
INNER JOIN #tempA2 ON #tempA.stringEntry = #tempA2.entryA
INNER JOIN #tempB2 ON #tempB2.RowNumber = #tempA2.RowNumber;

This assumes that you have equal number of rows in each table, as you indicated, or are okay with having the "excess" entries in your first table not being updated.

MySQL Row Ordering: How to set up and use a `position` column for row ordering?

Since you can only set one auto_increment column in a table, you can use INSERT INTO...SELECT statement to increment the maximum value of the position.

INSERT INTO groceries (label, position) 
SELECT "Instant Noodles", COALESCE(MAX(position),0) +1
FROM groceries;

• SQLFiddle Demo

Learning data.table - how to update values by row number and column name

Or you can do this:

x <- names(my_dt)[1:5]

my_dt[, (x) := lapply(.SD, as.numeric), .SDcols = x]

my_dt[2,  (x):= as.list(replacement)]

First we convert the target columns in my_dt to numeric. .SDcols represents the subset of columns in .SD that we are interested in. .SD holds all the columns in the data.table (except the ones used in by).

Once we convert the target columns to numeric, we update the values by reference.

Note: It is not necessary to define x beforehand, everything can be done on the fly. However, if you define x, you need to wrap it in () to make sure data.table doesn't look for the column x

Using row_number update column value using previous row value

You can use LAG function like below

select pk, from_d, 
          case when row_num = 1 then to_d else date_sub(lag(to_d) over (), 1) end as to_d, 
          row_num from table;

This will give you the desired result

+---+----------+----------+-------------------+
| pk|    from_d|      to_d|row_number_window_0|
+---+----------+----------+-------------------+
|111|2019-03-03|2019-03-03|                  1|
|111|2019-02-02|2019-03-02|                  2|
|111|2019-01-01|2019-02-01|                  3|
|222|2019-03-03|2019-03-03|                  1|
|222|2019-01-01|2019-03-02|                  2|
|333|2019-02-02|2019-02-02|                  1|
|333|2019-01-01|2019-02-01|                  2|
|444|2019-02-02|2019-02-02|                  1|
|555|2019-03-03|2019-03-03|                  1|
+---+----------+----------+-------------------+

Maintaining/updating record order in mysql

Doing this sort of thing for e.g. sales orders with line numbers maintained by the user, I've found it best to handle it in an array in the BL or UI. Usually they will want to adjust several records, and sometimes want to say "forget it". So the easiest might be to just wait until they hit the "OK" button (or your equivalent) and then write them all back with current ordering.

You may end up dealing with deletions, too, which is another problem you can handle the same way.

SQL Update with row_number()

With UpdateData  As
(
SELECT RS_NOM,
ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
FROM DESTINATAIRE_TEMP
)
UPDATE DESTINATAIRE_TEMP SET CODE_DEST = RN
FROM DESTINATAIRE_TEMP
INNER JOIN UpdateData ON DESTINATAIRE_TEMP.RS_NOM = UpdateData.RS_NOM

---

Related Topics