CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008t-sql
user1820705
user1820705

Reputation: 681

Sql automatically update

I have this view: CREATE VIEW AS

SELECT
         p.pr_id
        ,p.arenda
        ,p.PlotArea
        ,p.OwnershipTitle
        ,p.Price
        ,p.NotaryCosts
        ,p.AgentFee
        ,p.CtrNO
        ,isnull(p.Price,0)-isnull(a.Price,0) as Diferente
        ,isnull(p.Price,0)+isnull(p.NotaryCosts,0)+isnull(p.AgentFee,0) as TotalCosts

from  nbProcuri p
      left JOIN nbAchizitii a
      ON p.PlotArea = a.PlotArea and p.CtrNo=a.CtrNo
where a.CtrNO is null and a.PlotArea is null

I want to correlate those 2 tables with another one called Cadastrial where I have also a column called PlotArea. The column p.arenda should update itself with value 'yes' if p.PlotArea=c.PlotArea otherwise to fill with no. Is this possible somewhow? Thanks!

Upvotes: 1

Views: 83

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Like this:

UPDATE p
SET p.arenda = CASE WHEN p.PlotArea IS NULL THEN 'no' -- For not matched 
                    ELSE 'yes' -- for matched
               END
FROM nbProcuri p
LEFT JOIN Cadastrial c ON p.PlotArea = c.PlotArea
LEFT JOIN nbAchizitii a
      ON p.PlotArea = a.PlotArea and p.CtrNo = a.CtrNo
WHERE a.CtrNO IS NULL 
  AND a.PlotArea IS NULL;

Upvotes: 1

Related Questions