Update in three tables

Question

Good morning, I wonder if it is possible to update in three tables at once? I have the A, booklet, booklet, which are listed by the id_book column, and I would like to change the value of the book column in the three tables at the same time to 99.99 without having to update separately in each table, if possible also like to use the same logic with the Delete command.

Answer 1

For each table an update command should be used: UPDATE. It is simple to assemble. I suggest that you encapsulate the 3 commands with the pair BEGIN TRANSACTION / COMMIT

-- código #1
declare @id_livro ____, @preco_livro ____;
set @id_livro= ___;
set @preco_livro= 99.99;

BEGIN TRANSACTION;

UPDATE livrosA
  set preco_livro= @preco_livro
  where id_livro = @id_livro;

UPDATE livrosB
  set preco_livro= @preco_livro
  where id_livro = @id_livro;

UPDATE livrosC
  set preco_livro= @preco_livro
  where id_livro = @id_livro;

COMMIT;

Declare the variables @id_livro and @preco_livro with the same type of column data id_livro and preco_livro, respectively.

---

There are ways to make, for example, by changing the price of a book in the table livrosA the same book has its price changed in the tables livrosB and livrosC. It would be through the employment of trigger procedure (Trigger). However, for those who are starting SQL Server I suggest not use triggers.

Answer 2

You have two options to do this (in addition to what is already given by @José Diz):

• OUTPUT clause.
• TRIGGERS.

---

Here is an example with OUTPUT clause

CREATE TABLE BooksA(
  BookID INT,
  BookPrice MONEY
);

CREATE TABLE BooksB(
  BookID INT,
  BookPrice MONEY
);

CREATE TABLE BooksC(
  BookID INT,
  BookPrice MONEY
);

INSERT INTO BooksA VALUES
(1, 100),
(2, 200),
(3, 100);

INSERT INTO BooksB SELECT * FROM BooksA;
INSERT INTO BooksC SELECT * FROM BooksB;

DECLARE @Price MONEY = 500;
DECLARE @IDs TABLE(ID INT);

BEGIN TRAN
  UPDATE BooksA
  SET BookPrice = @Price
  OUTPUT INSERTED.BookID
  INTO @IDs
  WHERE BookPrice = 100;

  UPDATE BooksB
  SET BookPrice = @Price
  FROM BooksB BB INNER JOIN @IDs I 
  ON BB.BookID = I.ID;

  UPDATE BooksC
  SET BookPrice = @Price
  FROM BooksC BC INNER JOIN @IDs I
  ON BC.BookID = I.ID;
COMMIT TRAN

SELECT * FROM BooksA;
SELECT * FROM BooksB;
SELECT * FROM BooksC;

Demo

---

Here is an example with TRIGGERS:

CREATE TRIGGER Trig1
ON BooksA
AFTER UPDATE
AS
BEGIN
IF UPDATE(BookPrice)
  BEGIN
    UPDATE BooksB
    SET BookPrice = I.BookPrice
    FROM BooksB INNER JOIN INSERTED I
    ON BooksB.BookID = I.BookID;
    --
    UPDATE BooksC
    SET BookPrice = I.BookPrice
    FROM BooksC INNER JOIN INSERTED I
    ON BooksC.BookID = I.BookID;
  END
END

UPDATE BooksA
SET BookPrice = 500
WHERE BookPrice = 100;

SELECT * FROM BooksA;
SELECT * FROM BooksB;
SELECT * FROM BooksC;

Demo