D
D
Denis2019-12-24 22:16:17
SQL
Denis, 2019-12-24 22:16:17

How to write an update query to fetch data from another MS SQL table?

Good day, there are two databases, you need to write a request to update the data when calling a stored procedure.
My query option:

UPDATE TeamMembers SET name = (SELECT [Имя члена команды5] FROM Bids WHERE Id_bids = @id_bids), adress = (SELECT [Адрес5] FROM Bids WHERE Id_bids = @id_bids), id_position = (SELECT id_position FROM Positions, Bids WHERE namePosition = [Должность5] AND Bids.Id_bids = @id_bids), id_team = (SELECT id_team FROM Teams, Bids WHERE TeamName = [Название команды] AND Bids.Id_bids = @id_bids)

Base Codes:
CREATE TABLE [dbo].[Bids] (
    [Id_bids]            INT           IDENTITY (1, 1) NOT NULL,
    [Имя пользователя]   NVARCHAR (50) NULL,
    [Название команды]   NVARCHAR (50) NULL,
    [Название судна]     NVARCHAR (50) NULL,
    [Место ловли]        NVARCHAR (50) NULL,
    [Дата выхода]        DATETIME      NULL,
    [Дата возвращения]   DATETIME      NULL,
    [Имя члена команды1] NVARCHAR (50) NULL,
    [Адрес1]             NVARCHAR (50) NULL,
    [Должность1]         NVARCHAR (50) NULL,
    [Имя члена команды2] NVARCHAR (50) NULL,
    [Адрес2]             NVARCHAR (50) NULL,
    [Должность2]         NVARCHAR (50) NULL,
    [Имя члена команды3] NVARCHAR (50) NULL,
    [Адрес3]             NVARCHAR (50) NULL,
    [Должность3]         NVARCHAR (50) NULL,
    [Имя члена команды4] NVARCHAR (50) NULL,
    [Адрес4]             NVARCHAR (50) NULL,
    [Должность4]         NVARCHAR (50) NULL,
    [Имя члена команды5] NVARCHAR (50) NULL,
    [Адрес5]             NVARCHAR (50) NULL,
    [Должность5]         NVARCHAR (50) NULL,
    [Статус]             NVARCHAR (70) NOT NULL,
    CONSTRAINT [PK_Bids] PRIMARY KEY CLUSTERED ([Id_bids] ASC)
);

CREATE TABLE [dbo].[Teams] (
    [id_team]    INT           IDENTITY (1, 1) NOT NULL,
    [TeamName]   NVARCHAR (50) NULL,
    [id_sailing] INT           NULL,
    CONSTRAINT [PK_Teams] PRIMARY KEY CLUSTERED ([id_team] ASC),
    CONSTRAINT [FK_Teams_Sailings] FOREIGN KEY ([id_sailing]) REFERENCES [dbo].[Sailings] ([id_sailing]) ON DELETE CASCADE
);

CREATE TABLE [dbo].[Positions] (
    [Id_position]  INT           IDENTITY (1, 1) NOT NULL,
    [namePosition] NVARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id_position] ASC)
);

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2019-12-25
@tsklab

As it was already:

DECLARE @ID_TeamMembers INT
-- для Bids.[Имя члена команды1]
SELECT @ID_TeamMembers = ID FROM TeamMembers WHEREIF @ID_TeamMembers IS NULL
  INSERT INTO TeamMembers …
ELSE
  UPDATE TeamMembers SETWHERE ( ID = @ID_TeamMembers )
-- Повторить для Bids.[Имя члена команды2] и так далее

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question