CODEWITHSUNDEEP
sql-server
rajiv
rajiv

Reputation: 19

Using update with Join and case

I have 2 tables employee and transport

employee table:

name  organization vehicle
rajiv a            hyundai 
max   b            suzuki

transport table:

organization vehicle 
a            hyundai
b            suzuki

I want to add a column in employee table such that if a value exists in the vehicle column of transport table then value should be 1 else 0 of new column havingvehicle. Both tables can only be joined on basis of organization and vehicle.

Please suggest how can I accomplish these in update statement

alter table employee
ADD HAVINGVEHICLE VARCHAR(255)

Upvotes: 0

Views: 37

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

You may use a CASE expression here with EXISTS logic:

WITH cte AS (
    SELECT
        e.name,
        e.organization,
        e.vehicle,
        CASE WHEN EXISTS (SELECT 1 FROM transport t
                          WHERE t.organization = e.organization AND t.vehicle = e.vehicle)
             THEN 1 ELSE 0 END AS having_vehicle_new
    FROM employee e
)

UPDATE cte
SET having_vehicle = having_vehicle_new;

We could also a join/aggregation approach (query only shown):

SELECT
    e.name,
    e.organization,
    e.vehicle,
    CASE WHEN COUNT(t.organization) > 0 THEN 1 ELSE 0 END AS having_vehicle
FROM employee e
LEFT JOIN transport t
    ON t.organization = e.organization AND t.vehicle = e.vehicle
GROUP BY
    e.name,
    e.organization,
    e.vehicle;

Given that this new column is really just derived data, I advocate actually not creating a new column. Instead, just use one of the two select queries above, or maybe create a view.

Upvotes: 2

Related Questions