I have 2 tables :

Table1

• parcel_number
• pool_type

Table2

• parcel_number
• need_water

I would like to know if the table 1 "pool" column has a value than update the "need_water" column of the table 2 to yes.

Here what I would like to have for the table 2.

Table 1

Table 2

 if exists(select a.pool_type 
 from table1 a
    where a.parcel_number = b.parcel_number)
    Begin
        Update b
        set b.need_water = 'Yes'
        from table2 b
        end
   else
    Begin
        Update b
        set b.need_water = 'No'
        from table2 b
    End

Thank you

CodePudding user response：

A single update statement should be able to solve this:

UPDATE t2
    SET t2.need_water = CASE WHEN t1.parcel_number IS NOT NULL THEN 'Yes' ELSE 'No' END
FROM table2 t2
   INNER JOIN table1 t1
   ON t2.parcel_number = t1.parcel_number

CodePudding user response：

An UPDATE with a JOIN will work

UPDATE  t2
SET     need_water = 'yes'
FROM    Table1 t1
        JOIN Table2 t2
            ON t1.parcel_number = t2.parcel_number
WHERE   t1.pool_type IS NOT NULL