how do compare the status value from two different tables and update the second table in sql server

Question

HI Team how do i update the status values by comparing the two columns in the below here the status is Adj_Status and IMT_Status values are different then i need to update the IMT_Status from Adj_Status Please let us know ur inputs ,here these status are coming from two different tables(Adj_Status & IMT_Status),we can compare these status from two different tables and update the IMT_Status from adj_status

Answer 1

Erland is right, this would be much easier to answer if you could provide the two tables structures separately (and as code instead of as an image). I've assumed that you mean this:

CREATE TABLE #imt (
  id INT NOT NULL PRIMARY KEY
, [order] INT NOT NULL 
, IMT_Status VARCHAR(10) NOT NULL
);

INSERT INTO #imt (
  id
, [order]
, IMT_Status
) VALUES 
  (10266917, 2, 'XX')
, (10239944, 1, '61')
, (10256628, 2, '61')
, (10256623, 1, '61');

CREATE TABLE #adj (
  id INT NOT NULL PRIMARY KEY
, Adj_Status VARCHAR(10) NOT NULL
);

INSERT INTO #adj (
  id
, Adj_Status
) VALUES 
  (10266917, 'XX')
, (10239944, 'XX')
, (10256628, '61')
, (10256623, '61');

If that's correct, you can update #imt from #adj like this:

UPDATE tgt
SET IMT_Status = src.Adj_Status
FROM #adj src
  INNER JOIN #imt tgt ON tgt.id = src.id

Answer 2

Hi @Naresh y

compare the status value from two different tables and update the second table

Have you tried MERGE?

See this sample:

MERGE TargetProducts AS Target
USING SourceProducts AS Source ON Source.ProductID = Target.ProductID
WHEN NOT MATCHED BY Target THEN
    INSERT (ProductID,ProductName, Price) 
    VALUES (Source.ProductID,Source.ProductName, Source.Price);

Also, refer to this article for more details: SQL Server MERGE Statement overview and examples.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.