CODEWITHSUNDEEP
mysql
Eli
Eli

Reputation: 4359

mysql update only increments one time

i'm trying to create a query that will the increment the values from 1 column and update itself based on the max int value of the same column

update
    content
set
    ord = (
        select
            tempOrd
        from
            (
                select
                    max(ord) as 'tempOrd'
                from
                    content
            ) as temp
    ) + 1
where section_id = 'news.article'

(edit: removed redundant order by query)

there are 67 entries in the database, if the max(ord) is 10118 this will only update the column to 10119 for all entries.

i'm sorta expecting it to do 10119, 10120, 10121, 10122, ...

Upvotes: 0

Views: 35

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31812

update content c
cross join (select max(ord) as max_ord from content) mx
cross join (select min(ord) as min_ord from content where section_id = 'news.article') mn
set c.ord = ord + 1 + mx.max_ord - mn.min_ord
where c.section_id = 'news.article'

Upvotes: 1

Related Questions