Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 UPDATE + order by + set ROWCOUNT N  [new]
Молодой
Member

Откуда: Москва
Сообщений: 171
Надо обновить @NN первых записей таблицы TBL_1 упорядоченных по id пишу:

SET ROWCOUNT @NN
Update dbo.TBL_1 set Out_Flag = 1
where id in (SELECT id FROM dbo.TBL_1 WHERE (id_body = @id_body) AND Out_Flag = 0) order by id)

Выдает сообщение:
Server: Msg 1033, Level 15, State 1, Line 24
An ORDER BY clause is invalid in views, derived tables, and subqueries unless TOP is also specified.

Если закомментировать "order by id" все работает, но нужно чтоб работало с ним!
Как обойти эти грабли?
10 июн 04, 17:56    [735805]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Glory
Member

Откуда:
Сообщений: 104760
unless TOP is also specified.
10 июн 04, 17:57    [735812]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Молодой
Member

Откуда: Москва
Сообщений: 171
Так ведь в TOP переменную @NN не подставишь.
10 июн 04, 18:04    [735847]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
paparome
Member

Откуда: Москва
Сообщений: 4312
SELECT TOP 100 PERCENT id FROM dbo.TBL_1 WHERE (id_body = @id_body) AND Out_Flag = 0) order by id
?

только я не понял - зачем сортировка в селекте, который в условии используется - ведь на набор данных он не влияет?
10 июн 04, 18:07    [735859]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Молодой
Member

Откуда: Москва
Сообщений: 171
Так ведь в TOP переменную @NN не подставишь.
10 июн 04, 18:08    [735868]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Молодой
Так ведь в TOP переменную @NN не подставишь.


Так ведь и не надо!

Предложили TOP 100 PERCENT WITH TIES в подзапрос запхнуть - работает, проверено. Прими как должное.
10 июн 04, 18:16    [735890]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Молодой
Member

Откуда: Москва
Сообщений: 171
- зачем сортировка в селекте, который в условии используется - ведь на набор данных он не влияет
Как раз влияет: "Надо обновить @NN первых записей таблицы TBL_1 упорядоченных по id"
SELECT TOP 100 PERCENT id FROM dbo.TBL_1 WHERE (id_body = @id_body) AND Out_Flag = 0) order by id
сработает, а UPDATE как сделать на переменное количество ПЕРВЫХ строк?
10 июн 04, 18:16    [735892]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Glory
Member

Откуда:
Сообщений: 104760
Динамический запрос
или
временная таблица

Как больше нравится
10 июн 04, 18:18    [735900]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
paparome
Member

Откуда: Москва
Сообщений: 4312
Молодой
- зачем сортировка в селекте, который в условии используется - ведь на набор данных он не влияет
Как раз влияет: "Надо обновить @NN первых записей таблицы TBL_1 упорядоченных по id"
SELECT TOP 100 PERCENT id FROM dbo.TBL_1 WHERE (id_body = @id_body) AND Out_Flag = 0) order by id
сработает, а UPDATE как сделать на переменное количество ПЕРВЫХ строк?


А кто вам сказал, что Update будет обновлять в порядке сортировки подзапроса?

имхо - скидываем первые @nn id во временную таблицу и уже на основе нее делаем Update?
10 июн 04, 18:20    [735908]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Молодой
Member

Откуда: Москва
Сообщений: 171
Прошу прощения что тормозил. с
SELECT TOP 100 PERCENT
все правильно работает.
СПАСИБО!
10 июн 04, 18:30    [735939]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Glory
Member

Откуда:
Сообщений: 104760
Если в подзапросе будет стоять SELECT TOP 100 PERCENT, то зачем он тогда вообще нужен ?
Это будет все равно что
Update dbo.TBL_1 set Out_Flag = 1 where (id_body = @id_body) AND Out_Flag = 0
10 июн 04, 18:42    [735979]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Crimean
Member

Откуда:
Сообщений: 13148
2 Glory

А просто попробовать? :)
По условиям задачи надо изменить первые NN записей по достаточно хитрым условиям. Я бы, если честно, делал это через временную таблицу, но предложенный вариант тоже прокатит. При этом

declare @i int
select @i = 20

set rowcount @i

update users set name = name where id in
(select id from users where code like '2%' order by code)

set rowcount 0

Выдает

Server: Msg 1033, Level 15, State 1, Line 7
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

А при этом "безумная" с виду конструкция

declare @i int
select @i = 20

set rowcount @i

update users set name = name where id in
(select top 100 percent with ties id from users where code like '2%' order by code)

set rowcount 0

Как раз и приводит к достижению желаемого почти одним запросом :)
10 июн 04, 18:59    [736037]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Glory
Member

Откуда:
Сообщений: 104760
А что более нормальная конструкция не приведет к тому же результату ???

declare @i int
select @i = 20

set rowcount @i

update users set name = name where id in
where code like '2%'

set rowcount 0
10 июн 04, 19:01    [736043]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Молодой
Member

Откуда: Москва
Сообщений: 171
Crimean Прав. Я тоже не поверил что сие безумство будет работать. Но оно работает!
10 июн 04, 19:02    [736047]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Crimean
Member

Откуда:
Сообщений: 13148
2 Glory

Нет, не получится. Конечно, какие-то 20 записей изменятся. Но надо-то не какие-то, а именно первые 20!

Смотрим.

select top 10 id from sysobjects order by id

id
-----------
1
2
3
4
6
8
9
10
11
12

(10 row(s) affected)

Дальше

set rowcount 10
select top 100 percent with ties id from sysobjects order by id

id
-----------
1
2
3
4
6
8
9
10
11
12

(10 row(s) affected)

Еще комментарии нада? :)

P.S.Я много работаю с rowcount :)
10 июн 04, 19:14    [736083]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Glory
Member

Откуда:
Сообщений: 104760
Еще комментарии нада? :)
Т.е. закладываемся на то какой план выберет оптимизатор при соединении набора из подзапроса с обновляемой таблицей ?
Вполне резонно предположить что если план будет основан на сканировании результатов подзапроса с последующим обновлением записи в целевой таблице (nested loops) все именно так и будет - т.е. подзапрос вернет все(!) записи по которым мы пойдем по порядку указанному в его order. И дойдем до записи ограниченной set rowcount.
Вот только что будет если план запроса вдруг получится другим ???
10 июн 04, 19:20    [736091]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
a1ex
Member

Откуда: .ua
Сообщений: 369
Молодой
Crimean Прав. Я тоже не поверил что сие безумство будет работать. Но оно работает!

тока он еще более прав, когда говорит, что

Crimean
Я бы, если честно, делал это через временную таблицу ...

лично я отказался от этого способа именно в пользу временных таблиц, потому, что с ними гораздо удобнее манипулировать сложными подзапросами.
10 июн 04, 19:21    [736092]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Crimean
Member

Откуда:
Сообщений: 13148
2 Glory

:) Это интересный запрос. Получаем в итоге константное соединение из серии

select * from sysobjects a join sysobjects b on b.id = a.id

И если каждую из частей JOIN переделать в вид select top 20 ... order by Id , то это как раз и будет то , что мы делаем в этом "безумном" запросе. В общем, я не вижу даже вероятности того, что запрос отработает "неправильно". Хотя, конечно, "неправильно" работающий пример приветствуется :) То есть пример, который изменит не 20 записей, а меньше. Больше он изменить не сможет физически.
Ну и я свое мнение (насчет временных таблиц) уже высказал :)
10 июн 04, 19:26    [736104]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Glory
Member

Откуда:
Сообщений: 104760
То есть пример, который изменит не 20 записей, а меньше
Изменится меньше и больше чем указано в set rowcount понятное дело не сможет. Вопрос в том будут ли это всегда(!) именно первые NNN записей. А не произвольные NNN записей.
10 июн 04, 19:30    [736110]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Glory
Member

Откуда:
Сообщений: 104760
set rowcount 10

select *
from sysobjects a where id in(select top 100 percent id from sysobjects order by name desc)

select *
from sysobjects a where id in(select top 100 percent id from sysobjects order by name desc)
option(hash join)

select *
from sysobjects a with(index=2)where id in(select top 100 percent id from sysobjects order by name desc)

set rowcount 0
10 июн 04, 19:45    [736134]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Alexes
Member

Откуда:
Сообщений: 1100
Glory меня опередил с обратной сортировкой.
В общем set rowcount влияет только на top level запрос.
10 июн 04, 19:51    [736142]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Хмммм. Получается не то, чтобы на top level, а на ВЕСЬ запрос ЦЕЛИКОМ. И на то, как именно применить, мы повлиять как раз и не можем...
Очччень интересный опыт!
Может того, в ФАК?

P.S.

select top 10 * 
from sysobjects a where id in (select top 100 percent with ties id from sysobjects order by name desc)

select top 10 *
from sysobjects a where id in (select top 100 percent with ties id from sysobjects order by name desc)
option(hash join)

select top 10 *
from sysobjects a with(index=2) where id in (select top 100 percent with ties id from sysobjects order by name desc)

Дает ровно то же самое... Что многое объясняет.
10 июн 04, 20:05    [736160]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Glory
Member

Откуда:
Сообщений: 104760
Может того, в ФАК?
Ну тогда нужен какой-никакой Вопрос и к нему Ответ :)
10 июн 04, 20:15    [736176]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Вопрос - как изменить первые 10 записей?
Ответ - _так_ не надо изменять первые 10 записей! :)
11 июн 04, 11:10    [737063]     Ответить | Цитировать Сообщить модератору
 Re: UPDATE + order by + set ROWCOUNT N  [new]
Молодой
Member

Откуда: Москва
Сообщений: 171
Я честно говоря, большие дядьки, не совсем понял о чем Вы тут ругались .
Лучше скажите, вот это будет ВСЕГДА правильно работать:?
SET ROWCOUNT @NN
create table #id_tmp (Tid int)
insert #id_tmp (Tid) SELECT id FROM dbo.TBL_1 WHERE (id_body = @id_body) AND (Out_Flag = 0) order by id
SET ROWCOUNT 0
Update dbo.TBL_1 set Out_Flag = 1 where id in (select Tid from #id_tmp)
drop table #id_tmp
11 июн 04, 12:02    [737258]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить