Answer the question
In order to leave comments, you need to log in
How to write an update query for a lot of data in ms sql?
Greetings, I am writing a stored procedure that, when a request is confirmed, adds or updates data depending on their existence. This function adds team members from the fields of another table. Adding works, but there are problems with update. How can I implement multiple update . Tried through case it is impossible.
Function code:
USE [fishing_Firm]
GO
/****** Object: StoredProcedure [dbo].[insertBids] Script Date: 21.12.2019 16:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertBids] @id_bids int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if not exists (select id_sailing from Sailings, Bids where date_sail = Bids.[Дата выхода] and date_return = Bids.[Дата Возвращения] and Bids.Id_bids = @id_bids)
BEGIN
insert into Sailings (date_sail, date_return, id_boat) values ((select [Дата выхода] from Bids where Bids.Id_bids = @id_bids), (select [Дата возвращения] from Bids where Bids.Id_bids = @id_bids), (select id_boat from BoatPassport, Bids where name = Bids.[Название судна] and Bids.Id_bids = @id_bids))
END
else
BEGIN
update Sailings set date_sail = (select [Дата выхода] from Bids where Bids.Id_bids = @id_bids), date_return = (select [Дата возвращения] from Bids where Bids.Id_bids = @id_bids), id_boat = (select id_boat from BoatPassport, Bids where name = Bids.[Название судна] and Bids.Id_bids = @id_bids) from (SELECT TOP 10 * FROM Sailings , Bids where Sailings.date_sail = Bids.[Дата выхода] and Sailings.date_return = BIds.[Дата возвращения] ) as Selected
where Sailings.id_sailing = Selected.id_sailing
END
if not exists (select TeamName from Teams, Bids where TeamName = Bids.[Название команды] and Bids.Id_bids = @id_bids)
BEGIN
insert into Teams (TeamName, id_sailing) values ((select Bids.[Название команды] from Bids where Bids.Id_bids = @id_bids), (select id_sailing from Sailings, Bids where Sailings.date_sail = Bids.[Дата выхода] and Sailings.date_return = Bids.[Дата Возвращения] and Bids.Id_bids = @id_bids))
END
else
BEGIN
update Teams set TeamName = (select Bids.[Название команды] from Bids where Bids.Id_bids = @id_bids), id_sailing = (select id_sailing from Sailings, Bids where Sailings.date_sail = Bids.[Дата выхода] and Sailings.date_return = Bids.[Дата Возвращения] and Bids.Id_bids = @id_bids) from Teams inner join Bids on TeamName = Bids.[Название команды]
END
if not exists (select name,adress from TeamMembers, Bids where (name = Bids.[Имя члена команды1] and adress = Bids.Адрес1 and Bids.Id_bids = @id_bids) or (name = Bids.[Имя члена команды2] and adress = Bids.Адрес2) or (name = Bids.[Имя члена команды3] and adress = Bids.Адрес3 and Bids.Id_bids = @id_bids) or
(name = Bids.[Имя члена команды4] and adress = Bids.Адрес4 and Bids.Id_bids = @id_bids) or (name = Bids.[Имя члена команды5] and adress = Bids.Адрес5 and Bids.Id_bids = @id_bids))
BEGIN
insert into TeamMembers (name,adress, id_position, id_team) values ( (select Bids.[Имя члена команды1] from Bids where Bids.Id_bids = @id_bids), (select Bids.Адрес1 from Bids where Bids.Id_bids = @id_bids), (select id_position from Positions, Bids where namePosition = Bids.Должность1 and Bids.Id_bids = @id_bids), (select id_team from Teams, Bids where TeamName = Bids.[Название команды] and Bids.Id_bids = @id_bids)),
( (select Bids.[Имя члена команды2] from Bids where Bids.Id_bids = @id_bids), (select Bids.Адрес2 from Bids where Bids.Id_bids = @id_bids), (select id_position from Positions, Bids where namePosition = Bids.Должность2 and Bids.Id_bids = @id_bids), (select id_team from Teams, Bids where TeamName = Bids.[Название команды] and Bids.Id_bids = @id_bids)),
( (select Bids.[Имя члена команды3] from Bids where Bids.Id_bids = @id_bids), (select Bids.Адрес3 from Bids where Bids.Id_bids = @id_bids), (select id_position from Positions, Bids where namePosition = Bids.Должность3 and Bids.Id_bids = @id_bids), (select id_team from Teams, Bids where TeamName = Bids.[Название команды] and Bids.Id_bids = @id_bids)),
( (select Bids.[Имя члена команды4] from Bids where Bids.Id_bids = @id_bids), (select Bids.Адрес4 from Bids where Bids.Id_bids = @id_bids), (select id_position from Positions, Bids where namePosition = Bids.Должность4 and Bids.Id_bids = @id_bids), (select id_team from Teams, Bids where TeamName = Bids.[Название команды] and Bids.Id_bids = @id_bids)),
( (select Bids.[Имя члена команды5] from Bids where Bids.Id_bids = @id_bids), (select Bids.Адрес5 from Bids where Bids.Id_bids = @id_bids), (select id_position from Positions, Bids where namePosition = Bids.Должность5 and Bids.Id_bids = @id_bids), (select id_team from Teams, Bids where TeamName = Bids.[Название команды] and Bids.Id_bids = @id_bids))
END
else
BEGIN
END
if not exists (select Catches.id_catch from Catches,Bids, CatchPlacements where Catches.id_catchPlacements = CatchPlacements.id_catchPlacement and namePlacement = Bids.[Место ловли] and Bids.Id_bids = @id_bids)
BEGIN
insert into Catches (id_sailing,id_catchPlacements) values ((select id_sailing from Sailings, Bids where Bids.[Дата выхода] = Sailings.date_sail and Bids.[Дата возвращения] = Sailings.date_return and Bids.Id_bids = @id_bids), (select id_catchPlacement from CatchPlacements, Bids where namePlacement = Bids.[Место ловли] and Bids.Id_bids = @id_bids))
END
END
update TeamMembers set name = case when name = Bids.[Имя члена команды1] then Bids.[Имя члена команды1] when name = Bids.[Имя члена команды2] then Bids.[Имя члена команды2] when name = Bids.[Имя члена команды3] then Bids.[Имя члена команды3] when name = Bids.[Имя члена команды4] then Bids.[Имя члена команды4] else Bids.[Имя члена команды5] end, adress = case when adress = Bids.[Адрес1] then Bids.[Адрес1] when adress = Bids.[Адрес2] then Bids.[Адрес2] when adress = Bids.[Адрес3] then Bids.[Адрес3]
when adress = Bids.[Адрес4] then Bids.[Адрес4] else Bids.Адрес5 end, id_position = case when (select id_position from Positions) = (select id_position from Positions, Bids where namePosition = Bids.Должность1 and Bids.Id_bids = @id_bids) then (select id_position from Positions, Bids where namePosition = Bids.Должность1 and Bids.Id_bids = @id_bids) when (select id_position from Positions) = (select id_position from Positions, Bids where namePosition = Bids.Должность2 and Bids.Id_bids = @id_bids) then (select id_position from Positions, Bids where namePosition = Bids.Должность2 and Bids.Id_bids = @id_bids)
when (select id_position from Positions) = (select id_position from Positions, Bids where namePosition = Bids.Должность3 and Bids.Id_bids = @id_bids) then (select id_position from Positions, Bids where namePosition = Bids.Должность3 and Bids.Id_bids = @id_bids) when (select id_position from Positions) = (select id_position from Positions, Bids where namePosition = Bids.Должность4 and Bids.Id_bids = @id_bids) then (select id_position from Positions, Bids where namePosition = Bids.Должность4 and Bids.Id_bids = @id_bids) else
(select id_position from Positions, Bids where namePosition = Bids.Должность5 and Bids.Id_bids = @id_bids) end, id_team = (select id_team from Teams, Bids where TeamName = Bids.[Название команды] and Bids.Id_bids = @id_bids) from Bids
Answer the question
In order to leave comments, you need to log in
update TeamMembers set name =
no value. For each field, you need to write a separate subquery.
And use this construct:
DECLARE @ID_TeamMembers INT
SELECT @ID_TeamMembers = ID FROM TeamMembers WHERE …
IF @ID_TeamMembers IS NULL
INSERT INTO TeamMembers …
SET @ID_TeamMembers = IDENT_CURRENT( 'TeamMembers' ) -- чтобы не был NULL, может нужно будет
ELSE
UPDATE TeamMembers SET … WHERE ( ID = @ID_TeamMembers )
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question