запрос sql: если есть изменить запись если нет то добавить запись

← →
redlord (2005-07-17 21:26) [0]

всем привет
народ помогите составить запрос sql к базе mssql
имеется таблица с тремя полями f1 f2 f3 записи в f1 уникальны
мне нужно добавлять строку тока в том случае если значение из добавляемой строки в поле f1 не совпадает ни с одним значение поля F1 уже имеющихся в таблице.а если совпадают то поменять у уже имеюжейся записи значение поля f2

← →
Desdechado © (2005-07-17 22:56) [1]

begin
insert...
exception
update
end;

можно оформить как кодовый блок в SQL.Text
при условии, что в структуре БД правильно прописаны CONSTRAINTS

← →
evvcom © (2005-07-18 09:24) [2]

Раньше в MSSQL exception не было, сейчас на скору руку тоже не нашел, так что мой вариант таков:
update ...
if @@ROWCOUNT = 0
insert ...

← →
Anatoly Podgoretsky © (2005-07-18 09:33) [3]

В MSSQL есть триггер INSTEAD OF

← →
MOA © (2005-07-18 09:36) [4]

Поскольку поле f1 - первичный ключ => индексировано, прозрачнее будет (я бы так и сделал)
IF EXISTS(SELECT * FROM t WHERE f1=..)
UPDATE ....
ELSE
INSERT ....
Удачи!

← →
Fay © (2005-07-18 09:38) [5]

2 Anatoly Podgoretsky © (18.07.05 9:33) [3]
А в Oracle есть BEFORE. И чего, как это поможет?

← →
Ega23 © (2005-07-18 09:38) [6]

Declare @X int Set @X=-1 Select @X=1 from Table1 where F1=... if @X=-1 begin Insert end else begin Update end

← →
Fay © (2005-07-18 09:40) [7]

2 MOA © (18.07.05 9:36) [4]
Удача явно потребуется, т.к. между
IF EXISTS(SELECT * FROM t WHERE f1=..)
и
UPDATE .... ELSE INSERT ....
запись может быть удалена/добавлена.

← →
MOA © (2005-07-18 09:45) [8]

2Fay ©
Согласен, на этот аспект внимания не обратил :(.

← →
evvcom © (2005-07-18 09:46) [9]

> MOA © (18.07.05 09:36) [4]
> Ega23 © (18.07.05 09:38) [6]

Ваши запросы в случае update делают 2 скана таблицы. Зачем, если можно обойтись одним?

← →
Fay © (2005-07-18 09:49) [10]

2 evvcom © (18.07.05 9:46) [9]
Внимательно жду(ждём) подробностей.

← →
Ega23 © (2005-07-18 09:52) [11]

2 Fay © (18.07.05 09:49) [10]

Ну формально-то он прав.
В первый раз ищет exists (в моём случае F1=@F1).
Во второй - ищет по where.
Если взять крайни случай, что интересующая нас запись самая последнеяя - получается действительно 2 скана.
Но: если это поле индексировано, то по какому алгоритму будет искаться F1=@F1?

← →
Fay © (2005-07-18 09:53) [12]

2 Ega23 © (18.07.05 9:52) [11]
index seek

← →
Anatoly Podgoretsky © (2005-07-18 09:56) [13]

Fay © (18.07.05 09:38) [5]
Ни чем, вопрос про MS SQL

← →
ЮЮ © (2005-07-18 09:58) [14]

>Удача явно потребуется, т.к. между
>IF EXISTS(SELECT * FROM t WHERE f1=..)
>и
>UPDATE ....
>запись может быть удалена/добавлена

Если в рамках одной транзакции, а между ними нет закрытия ожной и старта новой транзакции, то никуда она не денется

← →
Fay © (2005-07-18 10:00) [15]

2 ЮЮ © (18.07.05 9:58) [14]
Денется, ещё как.

2 Anatoly Podgoretsky © (18.07.05 9:56) [13]
А что Вы имели ввиду, говоря про наличие таких триггеров?

← →
Fay © (2005-07-18 10:00) [16]

2 ЮЮ © (18.07.05 9:58) [14]
... или появится

← →
Anatoly Podgoretsky © (2005-07-18 10:01) [17]

Она естественно никуда не денется, но может оказаться добавленой в промежутке между SELECT и INSERT

← →
evvcom © (2005-07-18 10:06) [18]

> Внимательно жду(ждём) подробностей.

А чего непонятно? В обоих случаях используется сначала select (1-ый скан), потом update (2-ой скан). А индексы тут не при чем, они на количество сканов никак не влияют. На продолжительность (объем перебора и т.п.) да, но на количество...

← →
Fay © (2005-07-18 10:17) [19]

2 evvcom © (18.07.05 10:06) [18]
Честно говоря, я ждал Вышего решения задачи, а не проповеди.

2 ЮЮ © (18.07.05 9:58) [14]
Открываем QA. В одном окне пишем
use pubs go if object_id("QWE") is not null drop table QWE go create table QWE( CODE int not null, NAME varchar(32) collate SQL_Latin1_General_CP1251_CI_AS not null, constraint PK_QWE primary key nonclustered(CODE), constraint UNQ_QWE_NAME unique(NAME) ) go insert into QWE (CODE, NAME) values (1, "1") insert into QWE (CODE, NAME) values (2, "2") insert into QWE (CODE, NAME) values (3, "3") go declare @n int, @s varchar(32), @dt datetime select @n = 2, @s = "Два", @dt = "00:00:05" begin tran if exists(select * from QWE where CODE = @n) begin waitfor delay @dt update QWE set NAME = @s where CODE = @n end else begin waitfor delay @dt insert into QWE (CODE, NAME) values (@n, @s) end select [row count] = @@rowcount commit tran go drop table QWE
, а в другом
use pubs delete from QWE where CODE = 2

Запускаем сначала 1-й, и пока он думает, 2-й скрипт.
Внимательно смотрим на [row count]

← →
Ega23 © (2005-07-18 10:20) [20]

2 Fay © (18.07.05 10:17) [19]

Ты меня опередил... :о)

← →
evvcom © (2005-07-18 10:29) [21]

> Честно говоря, я ждал Вышего решения задачи, а не проповеди

А [2] что такое?

← →
MOA © (2005-07-18 11:02) [22]

Прошло вот так:
CREATE TABLE [t4] ( [f1] [int] NOT NULL , [f2] [int] NULL , [f3] [int] NULL , CONSTRAINT [PK_t4] PRIMARY KEY CLUSTERED ( [f1] ) ON [PRIMARY] ) ON [PRIMARY] GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION IF EXISTS(SELECT * FROM t4 WITH (UPDLOCK) WHERE f1=3) BEGIN WAITFOR DELAY "00:00:05" UPDATE t4 SET f2=223 WHERE f1=3 END ELSE BEGIN WAITFOR DELAY "00:00:05" INSERT INTO t4 (f1, f2, f3) VALUES (3, 203, 303) END COMMIT TRANSACTION
Т.е. с уровнем транзакции SERIALIZABLE и хинтом на экзисте UPDLOCK - всё ждёт как надо кого надо ;).

← →
Ega23 © (2005-07-18 11:03) [23]

2 evvcom © (18.07.05 10:29) [21]
А [2] что такое?

В [2] ты привязываешься к глобальной переменной @@ROWCOUNT, что может быть чревато последствиями. Нехорошими.

>В [2] ты привязываешься к глобальной переменной @@ROWCOUNT

Может тогда и @@ERROR небезопасно смотреть, ведь там может оказаться чужая ошибка :)
if @@ROWCOUNT = 0 - впримере из BOL стоит

Честно говоря, хотелось бы автора услушать, откуда такое желание возникло, точнее откуда это f1 взялось.

Если из этой же базы, то к чему такие извращения. Ведь если f1 определен, то запись есть и её надо редактировать. Даже если её уже не стало, то зачем вставлять, если её только что кто-то удалил? А если запись новая, то откуда f1 на клиенте? Его должен тогда сервер определить, ибо два клиента по одному и тому же алгоритму получат одно и тоже значение для новой записи, а это не лучше, чем в случае Fay © (18.07.05 10:17) [19]

Если же это внешние данные, то ситуация описанная в [19]
тоже притянута за уши, т.к. если из двух разных пакетов путаются вставить разные записи (разные f2 или f3 для одинакового f1), то это уже ошибка, независимо от поведения сервера, а если одинаковые, то не важно из какого пакета она добавится. А добавление 2х записей невозможно ввиду " f1 уникальны"

> что может быть чревато последствиями. Нехорошими

Сейчас в хелпе этого не увидел, но раньше читал, что если читать из @@ROWCOUNT сразу же за селектом, апдейтом и т.п., то это гарантирует, что она покажет именно нужное значение. А иначе зачем было мелкомягким вводить такую переменную, если ей нельзя доверять на 100%?

А [6], кстати, может быть чревато последствиями. Нехорошими
еще с большей вероятностью. Имхо.

> WAITFOR DELAY "00:00:05"

А это зачем? Искусственные тормоза? Делай [2] и хватит ерундой заниматься с кучей лишних запросов, да еще искусственных задержек.

>Fay © (18.07.05 11:34) [28]
>INSERT-ом проверял?
Угу. Во втором сеансе запускалось почти то же:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION IF EXISTS(SELECT * FROM t4 WITH (UPDLOCK) WHERE f1=3) BEGIN WAITFOR DELAY "00:00:05" UPDATE t4 SET f2=2223 WHERE f1=3 END ELSE BEGIN WAITFOR DELAY "00:00:05" INSERT INTO t4 (f1, f2, f3) VALUES (3, 2003, 3003) END COMMIT TRANSACTION
Результат - всё как надо, ждёт инсерта с первого сеанса, потом апдейтит (записи с f1=3 в таблице первоначально нет).

>evvcom © (18.07.05 11:38) [29]
>> WAITFOR DELAY "00:00:05"
>А это зачем?
А это чтобы наглядно проверить (чтобы кнопулю успеть нажать во втором сеансе), что транзакции отрабатывают как надо - моделируем так сказать, ситуацию, когда два процесса проверили - и оба получили, что записи нет - оба пытаются вставить, что нехорошо :).

Сам проверил - всё ОК.
Остаётся только тщательней следить за захватом ресурсов воизбежание deadlock-ов.

← →
redlord (2005-07-18 23:51) [33]

очен жалко что позно зашол на ветку да и сечас уже поздно а завтра обязательно изучу все посты
спасибо всем откликнувшимся

запрос sql: если есть изменить запись если нет то добавить запись Найти похожие ветки