SQL Обновить поле int в таблице инкрементируя начальное значение?

Question

[acspro]

Вот пока получилось так.

update `cases` 
			cross join (select @rownumber := 0) r
			set `ordered` = (@rownumber := @rownumber + 1)

--------------------------
Но проблема в том что нужно чтобы обновляло по списку отсортированному `ordered` ASC
пробовал добавить что то типа

WHERE `cases`.`id` IN (

					SELECT `cases`.`id` FROM `cases` 
					ORDER BY `cases`.`ordered` ASC  
					)

Уважаемые гуру MYSQL, подскажите как сделать правильно?
------
Вот код который делает то что нужно кучей запросов.

public function normalize(){
		$item=0;
		$arr=$this->db->squery('SELECT `ordered`,`id` FROM `cases` order by `ordered` ASC',true);

		if ($arr){
			foreach ($arr as $k=>$v){
				$this->db->query('UPDATE `cases` SET `ordered`='.intval($item).' WHERE `id`='.intval($v['id']).' ');
				$item++;
				}
			}


		}

Answer 1

[Дмитрий Энтелис]

Я стесняюсь спросить - а зачем вообще это? Не проще на бекенде делать нумерацию строк если уж так надо?
Или в чем вообще задача?

Comments

[acspro]

Вот есть таблица - в ней есть поле сортировка `ordered` в зависимости от этого поля выводится отсортировано несколько блоков на странице. А это требуется для нормализации списка - чтобы при определенных условия восстановить нумерацию в порядке 0 1 2 3 4 ели скажем был удален промежуточный блок и не было 0 1 3 4. А на бакенде что сделать? какую нумерацию? Можно тупо выбрать отсортированный массив и и по нему пройтись UPDATE это понятно. Но нужно так как я написал. Поэтому и вопрос к Гуру))))

[Станислав Макаров]

Назначение поля ordered честно говоря так и осталось загадкой, такое ощущение что оно впилено вообще непонятно для каких целей. Если вам нужно "восстанавливать" нумерацию, то может нафиг не надо ее вообще где-то ХРАНИТЬ? человек вам говорит эту нумерацию генерировать тем кодом, который читает эти данные из базы. Если кому-то в интерфейсе нужен список и номера строк в нем, и эти номера НЕ привязаны к самим строкам (т.е. не являются данными с точки зрения предметной области), как в вашем случае, то никто не хранит эту нумерацию в базе, она генерируется по ходу дела. вы просто выбираете запросом c ORDER BY нужные вам строки, а потом, когда показываете их, уже добавляете номера, и все. Грубо говоря, далеко не все что у вас показывается в HTML-табличке, ну или в DataGrid каком-нибудь, должно находиться в SQL-таблице.

[acspro]

Станислав Макаров: Логично. Но мы ушли от сути. Поле `ordered` отвечает за вывод блоков в нужном порядке. И как понять номера не привязаны к строкам? У каждой строки свой номер порядковый по которому она выводится. Ща вот смотрите как. На фронтенд висит вывод нескольких html блоков так называемых кейсов - которые в сущности некоторая информация + статья на кейс. Важен порядок вывода этих кейсов. Порядок редактируется в админке. С этим проблем нету. Там я как сделал drag and drop просто переносятся строки в таблице и все это сериализуется с базой по полю `ordered`. Ну и естественно эти кейсы можно удалять в админке и добавлять и изменять и менять порядок сортировки. Так во тв некоторых случаях нарушается нумерация сортировки - то есть возникают такие цепочки как 3 4 5 10 11 12 13 14 26. А хотелось бы сделать нормализацию этого поля таким образом чтобы во первых в том же порядке были эти поля при сортировке по полю `ordered` ну и значения были 0 1 2 3 4 5 6 7 Сейчас и так все прекрасно работает. Просто тут решил попробовать 1 sql запросом сделать. Но знаний просто не хватает.

[acspro]

acspro: Сейчас все это получается но как сделать update в нужной последовательности одним запросом. Вот то мне интересно. А в детали архитектуры вникать особенно не зачем. Мой вопрос касается именно SQL.

[Станислав Макаров]

как раз вникнуть в архитектуру стоит, если выясняется что на SQL нужно городить огород. Базе потом ваши запросы выполнять еще)). Если вам важен порядок, попробовали бы хранить id предудыщего кейса, после которого стоит текущий. Т.е. если кейс с id=16 стоит после кейса с id=19 то и хранили бы prev_id=19 у 16-го кейса. Так вы сохраните именно порядок. Нормализовать вам сейчас нужно изза того, что вы храните данные не о взаимном порядке, а фактические индексы ваших кейсов, КАЖДЫЙ из которых зависит от ДРУГИХ и при изменении одного (удаление/перестановка одного из кейсов) вам нужно менять все, а не одну/две записи (иными словами, в каждом индексе у вас избыточность). Это и приводит к необходимости нормализации этих индексов.
Если не хотите ничего менять, по SQL-запросу пока не могу подсказать, т.к. запрос в этом случае будет императивный: у вас содержимое ordered в одной записи зависит от того, что находится в другой. Пахнет циклом, так что я советую вам не морочиться, а сделать хранимую процедуру, запилить в ней обычный цикл, и вызывать хранимку из php-кода при необходимости.

Answer 2

[He11ion]

Энжой ё костыль:

UPDATE `cases` SET `ordered` = `ordered` + 1
WHERE id = (SELECT a.`id` FROM (SELECT * FROM `cases` ORDER BY `ordered` ASC LIMIT 1) as a);

Comments

[acspro]

не не катит))) ща я приведу код php В котором делается то что мне нужно кучей sql запросов. Посмотри пожалуйста может что и получиться .

Answer 3

[Oleg]

а чем такое не подходит ? :)

set @rownumber := 0;
update `cases` 
      set `ordered` = (@rownumber := @rownumber + 1)
order by ordered ASC

Comments

[acspro]

да подходит, но придется 2 запроса выполнить. На php. А как запихать set@rownumber:=0 в один запрос с update?

[Oleg]

Ну, пользуясь вашей логикой можно так :)

update `cases`
cross join (select @rownumber := 0) r
set `ordered` = (@rownumber := @rownumber + 1)
order by ordered ASC

Правда не вижу особого смысла так делать, но "хозяин- барин" :)

[acspro]

Oleg: не работает в mysql так как мульти запрос order by нельзя

[Oleg]

acspro: Тогда легче всего 2 запросами. Тем более что первый очень лёгкий и быстрый