MySQL Query to Update Field to Max(Field) + 1

mysql query to update field to max(field) + 1

Try

UPDATE TABLE set field = ((SELECT selected_value FROM (SELECT MAX(field) AS selected_value FROM table) AS sub_selected_value) + 1) WHERE id in (1,3,5,6,8)

Mysql Update field with max value between 2 other fields

UPDATE myTable
SET weight=GREATEST(gweight,volweight);

Warning this will update every row.

See the manual page for GREATEST().

Other things to note:

CREATE TABLE xxx2
(   id int auto_increment primary key,
    col1 int null,
    col2 int null,
    col3 int null
);

INSERT xxx2(col1,col2) values (null,1),(1,2);

UPDATE xxx2
SET col3=GREATEST(col1,col2);
SELECT * FROM xxx2;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | NULL |    1 | NULL |
|  2 |    1 |    2 |    2 |
+----+------+------+------+

So NULL in a column does not make for a happy GREATEST(). If your columns are not NULLABLE then there is no worry for the above and you can ignore the below fix:

TRUNCATE xxx2;
INSERT xxx2(col1,col2) values (null,1),(1,2);

UPDATE xxx2
SET col3=GREATEST(COALESCE(col1,0),COALESCE(col2,0));
SELECT * FROM xxx2;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | NULL |    1 |    1 |
|  2 |    1 |    2 |    2 |
+----+------+------+------+

So COALESCE() would fix NULL issues.

MySQL - Update/Set a column in one table equal to MAX value from another table

How about a subselect?

UPDATE project
SET 
    project.i-date = (
        SELECT MAX(f-date)
        FROM schedule
        WHERE schedule.site = project.site
    ),
    project.status = 'complete'
WHERE project.site = 'site123'

MySQL update column value with max value from another column

You should find that this version works in MySQL:

update services s join
       (select service_id, MAX(AVERAGE_MEMORY) as maxmem
        from service_performance
        group by service_id
       ) sp
       on s.service_id = sp.service_id
    set s.MAX_VALUE = sp.maxmem;

Your version would work if it had the right table name in the where clauses:

update services
    set MAX_VALUE = (SELECT MAX(AVERAGE_MEMORY) AS SERVICE_ID
                     FROM service_performance
                     WHERE `services`.`SERVICE_ID` = `service_performance`.`SERVICE_ID`)
    where exists (select *
                  from service_performance 
                  where `services`.`SERVICE_ID` = `service_performance`.`SERVICE_ID`
                 );

I am assuming that update _services is a typo and should really be update services.

Update autoincrement field to highest value + 1

MySQL doesn't allow using table being updated in a from clause.
you can have it as sub query

UPDATE example_table
     SET id= (SELECT maxId
              FROM (SELECT MAX(id) + 1 AS maxId
                    FROM example_table
                   ) t
              )
    WHERE id = 1000;

MYSQL Updating row to maximum value of similar rows

http://sqlfiddle.com/#!9/f79a3/1

UPDATE t1 
INNER JOIN (SELECT name, MAX(`value`) max_value
           FROM t1 GROUP BY name) t2 
ON t1.name = t2.name
SET t1.value = t2.max_value;

Update field with max and min value in mysql

UPDATE your_table 
SET field = case when field > 0.75 
                 then 1
                 else field + 0.25
            end

Especially in MySQL you can do

UPDATE your_table 
SET field = least(1, field + 0.25)

For values that can also be negativ you can use

update your_table
SET field = case when field + ? > 1.0 then 1 
                 when field + ? < 0.0 then 0 
                 else field + ? 
            end

---

Related Topics