MySQL Procedure to Update Numeric Reference in Previous Rows When One Is Updated

mysql procedure to update numeric reference in previous rows when one is updated

There are two cases to consider, I think:

1. Move one row so it appears earlier in the ordering.
2. Move one row so it appears later in the ordering.

It is non-trivial either way. It is not clear whether there is a unique constraint on the column 'order'; the end result is certainly supposed to have a unique ordering.

Notation:

• 'On' refers to the row with value 'order = n' in the old values
• 'Nn' refers to the row with 'order = n' in the new values

In the example (illustrative of case 1):

• O3 --> N1
• O1 --> N2
• O2 --> N3

As an alternative, consider moving id = 2 so it has order = 4:

• O2 --> N4
• O3 --> N2
• O4 --> N3

You are basically adding or subtracting one from the 'other' rows, where those are the rows in the old order between the old position of the moved row and the new position of the moved row. In a pseudo-code, using $old and $new to identify the before and after positions of the moved row, and dealing with case 1 ($old > $new):

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               WHEN order >= $new AND order < $old THEN order + 1
               END CASE
 WHERE order BETWEEN $new AND $old;

The corresponding code for case 2 ($old < $new) is:

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               WHEN order > $new AND order <= $old THEN order - 1
               END CASE
 WHERE order BETWEEN $old AND $new;

Given the WHERE clause on the UPDATE as a whole, you may be able to remove the second WHEN in the CASE and replace it with a simple ELSE.

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               ELSE                   order + 1
               END CASE
 WHERE order BETWEEN $new AND $old;

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               ELSE                   order - 1
               END CASE
 WHERE order BETWEEN $old AND $new;

I think a stored procedure is in order - choosing between the two statements based on the input parameters $old, $new. You might be able to do something with a judicious mix of expressions such as '($old - $new) / ABS($old - $new)' and 'MIN($old, $new)' and 'MAX($old, $new)' where the MIN/MAX are not aggregates but comparator functions for a pair of values (as found in Fortran, amongst other programming languages).

Note that I am assuming that while a single SQL statement is executing, the uniqueness constraint (if any) is not enforced as each row is changed - only when the statement completes. This is necessary since you can't actually control the order in which the rows are processed. I know of DBMS where this would cause trouble; I know of others where it would not.

---

It can all be done in a single SQL statement - but you do want a stored procedure to sort out the parameters to the statement. I use IBM Informix Dynamic Server (11.50.FC6 on MacOS X 10.6.2), and that is one of the DBMS that enforces the unique constraint on the 'order' column at the end of the statement. I did the development of the SQL without the UNIQUE constraint; that worked too, of course. (And yes, IDS does allow you to roll back DDL statements like CREATE TABLE and CREATE PROCEDURE. What did you say? Your DBMS doesn't? How quaint!)

BEGIN WORK;
CREATE TABLE AnonymousTable
(
    id      INTEGER NOT NULL PRIMARY KEY,
    title   VARCHAR(10) NOT NULL,
    order   INTEGER NOT NULL UNIQUE
);
INSERT INTO AnonymousTable VALUES(1, 'test1', 1);
INSERT INTO AnonymousTable VALUES(2, 'test2', 2);
INSERT INTO AnonymousTable VALUES(3, 'test3', 3);
INSERT INTO AnonymousTable VALUES(4, 'test4', 4);

SELECT * FROM AnonymousTable ORDER BY order;

CREATE PROCEDURE move_old_to_new(old INTEGER, new INTEGER)
    DEFINE v_min, v_max, v_gap, v_inc INTEGER;
    IF old = new OR old IS NULL OR new IS NULL THEN
        RETURN;
    END IF;
    LET v_min = old;
    IF new < old THEN
        LET v_min = new;
    END IF;
    LET v_max = old;
    IF new > old THEN
        LET v_max = new;
    END IF;
    LET v_gap = v_max - v_min + 1;
    LET v_inc = (old - new) / (v_max - v_min);
    UPDATE AnonymousTable
       SET order = v_min + MOD(order - v_min + v_inc + v_gap, v_gap)
     WHERE order BETWEEN v_min AND v_max;
END PROCEDURE;

EXECUTE PROCEDURE move_old_to_new(3,1);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(1,3);
SELECT * FROM AnonymousTable ORDER BY order;

INSERT INTO AnonymousTable VALUES(5, 'test5', 5);
INSERT INTO AnonymousTable VALUES(6, 'test6', 6);
INSERT INTO AnonymousTable VALUES(7, 'test7', 7);
INSERT INTO AnonymousTable VALUES(8, 'test8', 8);

EXECUTE PROCEDURE move_old_to_new(3,6);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(6,3);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(7,2);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(2,7);
SELECT * FROM AnonymousTable ORDER BY order;

ROLLBACK WORK;

The pairs of invocations of the stored procedure with the numbers reversed reinstated the original order each time. Clearly, I could redefine the v_inc variable so that instead of being just ±1, it was 'LET v_inc = v_inc - v_min + v_gap;' and then the MOD expression would be just 'MOD(order + v_inc, v_gap)'. I've not checked whether this works with negative numbers.

Adaptation to MySQL or other DBMS is left as an exercise for the reader.

Update the total based on the previous row of balance

Here comes a solution with assist of one user variable.

The result is verified with the full demo attached.

SQL:

-- data preparation for demo
create table tbl(Name char(100), id int, Col1 int, Col2 int, Col3 char(20), Col4 char(20), Total int, Balance int);
insert into tbl values
('Row1',1,6,1,'A','Z',0,0),
('Row2',2,2,3,'B','Z',0,0),
('Row3',3,9,5,'B','Y',0,0),
('Row4',4,12,8,'C','Y',0,0);
SELECT * FROM tbl;

-- Query needed
SET @bal = 0;
UPDATE tbl
SET
    Total = CASE    WHEN Col3 = 'A' and Col4 <> 'Z'
                        THEN Col1+Col2
                    WHEN Col3 = 'B' and Col4 <> 'Z'
                        THEN Col1-Col2
                    WHEN Col3 = 'C' and Col4 <> 'Z'
                        THEN Col1*Col2
                    ELSE 0 END,
    Balance = (@bal:=@bal + Total);
SELECT * FROM tbl;

Output(as expected):

mysql> SELECT * FROM tbl;
+------+------+------+------+------+------+-------+---------+
| Name | id   | Col1 | Col2 | Col3 | Col4 | Total | Balance |
+------+------+------+------+------+------+-------+---------+
| Row1 |    1 |    6 |    1 | A    | Z    |     0 |       0 |
| Row2 |    2 |    2 |    3 | B    | Z    |     0 |       0 |
| Row3 |    3 |    9 |    5 | B    | Y    |     0 |       0 |
| Row4 |    4 |   12 |    8 | C    | Y    |     0 |       0 |
+------+------+------+------+------+------+-------+---------+
4 rows in set (0.00 sec)

mysql> -- Query needed
mysql> SET @bal = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE tbl
    -> SET
    ->     Total = CASE    WHEN Col3 = 'A' and Col4 <> 'Z'
    ->                         THEN Col1+Col2
    ->                     WHEN Col3 = 'B' and Col4 <> 'Z'
    ->                         THEN Col1-Col2
    ->                     WHEN Col3 = 'C' and Col4 <> 'Z'
    ->                         THEN Col1*Col2
    ->                     ELSE 0 END,
    ->     Balance = (@bal:=@bal + Total);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 4  Changed: 2  Warnings: 0

mysql>
mysql> SELECT * FROM tbl;
+------+------+------+------+------+------+-------+---------+
| Name | id   | Col1 | Col2 | Col3 | Col4 | Total | Balance |
+------+------+------+------+------+------+-------+---------+
| Row1 |    1 |    6 |    1 | A    | Z    |     0 |       0 |
| Row2 |    2 |    2 |    3 | B    | Z    |     0 |       0 |
| Row3 |    3 |    9 |    5 | B    | Y    |     4 |       4 |
| Row4 |    4 |   12 |    8 | C    | Y    |    96 |     100 |
+------+------+------+------+------+------+-------+---------+
4 rows in set (0.00 sec)

Mysql update where each updated row has an increasing number

You can do something like this

UPDATE mytable e,
   (SELECT @n := 4) m
SET e.myCustomValue = @n := @n + 1 
WHERE e.myMatchValue ='a'

SQL-Fiddle

Update Value based on previous row in MySQL

You can use a JOIN and window functions in an UPDATE. One method is:

update t join
       (select t.*, lag(number) over (order by cid) as prev_number
        from t
       ) tt
       on tt.cid = t.cid
    set rum = (number - prev_number) * 100 / prev_number;

MySQL update column based on previous row (same column)

You can use an UPDATE with a JOIN to a derived table for this:

UPDATE Items AS i1
JOIN (
  SELECT ID, @n := @n + 1 AS Image
  FROM Items
  CROSS JOIN (SELECT @n := (SELECT MAX(Image) FROM Items)) AS v
  WHERE Image IS NULL
  ORDER BY ID
) AS i2 ON i1.ID = i2.ID
SET i1.Image = i2.Image;

The derived table uses variables in order to calculate the Image values of the records having NULLs.

Demo here

Mysql: update current value if previous row are same with current row

As far as I remember, MySQL has problems to select from the same table in an update statement. And this is what you would have to do, because in order to update a record or not, you'd have to select its previous record from the same table.

So create a temporary table, give it row numbers, then select from it with a self join, to compare each record with its previous record.

create temporary table temp
(
  rownum int, 
  dihistoryid int, 
  dih_qty_balance int
) engine = memory;

set @num = 0;

insert into temp
  select 
    @num := @num + 1 as rownum, 
    dihistoryid, 
    dih_qty_balance 
  from mytable
  order by dihistoryid;

update mytable
set dih_qty_balance = dih_qty_balance - dih_reorder_qty
where dihistoryid in
(
  select current.dihistoryid
  from temp current
  join temp previous on previous.rownum = current.rownum - 1
  where previous.dih_qty_balance = current.dih_qty_balance
);

drop temporary table temp;

Mysql query with one or more rows one status takes precedence Mysql 5.7

If your version of MySql is 8.0+ you can use window function ROW_NUMBER():

SELECT t.employee_email, t.status, t.id
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY employee_email ORDER BY status = 'active' DESC, id) rn
  FROM tablename
) t
WHERE t.rn = 1

For previous versions:

SELECT t.employee_email, MAX(t.status) status, MIN(t.id) id
FROM tablename t
WHERE t.status = 'active'
OR NOT EXISTS (SELECT 1 FROM tablename WHERE employee_email = t.employee_email AND status = 'active')
GROUP BY t.employee_email

See the demo.

Results:

> employee_email | status     | id
> :------------- | :--------- | -:
> test1@test.com | active     |  1
> test2@test.com | active     |  3
> test3@test.com | terminated |  4
> test4@test.com | terminated |  5

SQL - Update multiple records in one query

Try either multi-table update syntax

UPDATE config t1 JOIN config t2
    ON t1.config_name = 'name1' AND t2.config_name = 'name2'
   SET t1.config_value = 'value',
       t2.config_value = 'value2';

Here is a SQLFiddle demo

or conditional update

UPDATE config
   SET config_value = CASE config_name 
                      WHEN 'name1' THEN 'value' 
                      WHEN 'name2' THEN 'value2' 
                      ELSE config_value
                      END
 WHERE config_name IN('name1', 'name2');

Here is a SQLFiddle demo

Update value based on value in previous row

You can use lag to reference the previous row's value. In your example data value would remain null though as your case logic depends on the previous row which is null to start.

However, this is how you might approach it with an updatable CTE

with u as (
    select [Date], PersonId,
    case
        when Amount > 200 and Lag(Value) over(partition by PersonId order by [Date]) = 1 then 2
        when Amount > 500 and Lag(Value) over(partition by PersonId order by [Date]) = 2 then 3
        when [Date] > '20200101' and Amount = 500 and Lag(Value) over(partition by PersonId order by [Date])= 2 then 4
    end NewValue
    from Persons
)
update u set Value = NewValue;

---

Related Topics