0

I have 4 tables: tb1, tb2, tb3, tb4.

I want to return all the columns in 4 tables:

SELECT tb1.col1a, tb1.col1b, tb2.col2a, tb3.col3a, tb3.col3b, tb4.col4a
FROM tb1, 
INNER JOIN tb2 ON tb1.col1a=tb2.col2a
INNER JOIN tb3 ON tb1.col1a=tb3.col3a
INNER JOIN tb4 ON tb3.col3a=tb4.col4a

The result is a new table, we'll call it be tbX.

Now, I want to update a column col3b of tb3 with condition to base on tbX, what do I have to?

Gholamali Irani
  • 4,391
  • 6
  • 28
  • 59
Hungphieu
  • 11
  • 1
  • 4
  • Read about `update set ... from .. join ...` – Jens Jan 18 '18 at 10:08
  • btw the condition is not from tbx but from one of the joined tables. so it can be much easier to just join tb2 with the one that contains the data you want to use for update – Lelio Faieta Jan 18 '18 at 10:10
  • 1
    Possible duplicate of [How do I UPDATE from a SELECT in SQL Server?](https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server) – Ahmad Jan 18 '18 at 10:10
  • Add some sample table data and the expected result - as formatted text (not images.) Perhaps 3 tables is enough when practicing? – jarlh Jan 18 '18 at 10:11
  • Possible duplicate of [How to do 3 table JOIN in UPDATE query?](https://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query) – Radim Bača Jan 18 '18 at 10:39

1 Answers1

0
I think you can update using CTE as below.
WITH TBX
AS
(
SELECT tb1.col1a,tb1.col1b,tb2.col2a,tb3.col3a,tb3.col3b,tb4.col4a
FROM tb1
INNER JOIN tb2 ON tb1.col1a=tb2.col2a
INNER JOIN tb3 ON tb1.col1a=tb3.col3a
INNER JOIN tb4 ON tb3.col3a=tb4.col4a
)
UPDATE XYZ
SET XYZ.COLUMN = WHAT NEED TO BE REPLACED 
FROM XYZ
JOIN TBX
ON XYZ.COLNAME = TBX.COLNAME
user9192401
  • 168
  • 8