Increment Value in MySQL Update Query

Increment value in MySQL update query

Simply increment the value that already exists in the database

$sql = "UPDATE member_profile SET points = points + 1 WHERE user_id = ?";
$db->prepare($sql)->execute([$userid]);

This code would work for both PDO and mysqli in the modern PHP versions

MySQL update a field with an incrementing variable

Are you looking for something like this?

UPDATE textile_events e,
       (SELECT @n := 249) m
   SET e.seq_no = @n := @n + 1 
    WHERE e.eid = 'headsup' AND e.paid = 'paid'

SQLFiddle

How do I increment a value with mysql update query (php)

UPDATE tbl SET amt_field = amt_field + 1 WHERE ...

If you use the single quotes ', you're telling the enclosed value to be interpreted as a string You were probably thinking about the tick marks. This is also valid:

UPDATE tbl SET `amt_field` = `amt_field` + 1 WHERE ...

This must be used when the column (or table etc.) has a reserved name.

Update values incrementally in mysql

Try this:

mysql> select @i := 0;
mysql> update bar set c = (select @i := @i + 1);

Simple way to UPDATE rows with auto incrementing value in MySQL

We can Reset the @i value using the select statement.

 SELECT @i:=74;

As with the SELECT query above, we'll update ordering by name:

UPDATE Mytable SET value = @i:=@i+1 ORDER BY name;

And result "SELECT * FROM Mytable ORDER BY name"

+----------+--------+-----------+
| p_id     | name   | value     |
+----------+--------+-----------+
|        1 | Ben    |        74 |
|        2 | Luka   |        75 |
|        3 | Frank  |        76 |
|        4 | Divya  |        77 |
+----------+--------+-----------+

Source - Link

Update field with another auto increment field value MySQL

The approach you followed is not transaction safe as well.

The best option I can think about is to use trigger

Edit: According to @lagripe's mentionings

CREATE TRIGGER sometrigger
AFTER INSERT ON sometable
BEGIN
  SET NEW.post_id := (SELECT id from sometable order by DESC limit 1) + 1 ; // you may need +1 here. I couldn't test it.
END

or you may consider to use LAST_INSERT_ID

insert into table_name values ( .... );
update table_name set post_id = LAST_INSERT_ID();

but why do you need two columns with the same id at first place?

if you really need why don't you use computed/generated columns?

CREATE TABLE Table1(
  id DOUBLE,
  post_id DOUBLE as (id)
);

Updating a table to increment a value per user id

Fixing the table once can be done with an UPDATE:

SET @n = 0, @p = 1;

UPDATE node_items
SET position = (@p := IF(node_id=@n, @p+1, 1)),
    node_id = (@n := node_id)
ORDER BY node_id, id;

Making the table maintain the position values as you insert/update/delete data is harder. Basically, can't do it while allowing concurrent updates. You have to lock the table in every session that needs to do writes to the table. This makes concurrent sessions run serially.

You can read my old post about this here: Some sort of “different auto-increment indexes” per a primary key values

---

Related Topics