Update in three tables

Asked

Viewed 161 times

2

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.

  • 1

    in sql-server believe to be impossible. But why you have three tables (livrosA,livrosB,livrosC)? Wouldn’t it be better, for example, to have a table livros and another Categoria?? It may be interesting to review its structure!!

  • is that I am doing a training at the company where I work and created these tables only for study because my boss asked to research how to do this procedure if possible.

  • @Wilder The change in prices will be book by book or some criterion will be used to change a set of books at once?

2 answers

4

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.

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

  • The output clause would be that part OUTPUT INSERTED.BookID INTO @IDs, check? Do you have any documentation link to this? This is totally new to me, I wanted to study more, and preferably targeted

  • 1

    @Jeffersonquesado OUTPUT Clause ;)

  • @Sami The use of trigger procedure (Trigger) is closest to what Wilder requested, because he would have to change in a single table with the change reflected in the other tables. But the way Microsoft has implemented trigger procedures generates a number of pitfalls for T-SQL novices. The implementation for each Row (Mysql, Oracle Database) is much more natural to understand.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.