Update in three tables [closed]

Navigation
2

Good morning, I wonder if it's possible to update three tables at once? I have the tables booksA, booksB, booksC that are related by column book_id, and I would like to change the value of book_mode column in the three tables at the same time to 99.99 without having to do update separately on each table, if possible I would also like to use the same logic with the Delete command.

    
asked by anonymous 03.12.2018 / 13:49

2 answers

4

For each table you must use an update command: UPDATE. It's simple to assemble. I suggest that you encapsulate the 3 commands with the 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 data type of columns id_livro and preco_livro , respectively.

For example, when you change the price of a book in the livrosA table, the same book has its price changed in the livrosB and livrosC tables. It would be through the use of a trigger procedure ( trigger ). However, for those of you starting SQL Server, I suggest you do not use triggers.

    
03.12.2018 / 14:44
3

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

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

    
03.12.2018 / 16:52
Object Oriented Multithread in C ++ 11 Native? Return and console.log do not work as expected in Codecademy