Simple Question on SQL

[DB2Problem]

I have two tables as shown below -

Table 1 - emp_rec table

Empl_ID Empl_Name On_Vacation
200 Jhon Y
300 Sean Y
400 Bill N

Table 2 - emp_table

Empl_ID Empl_Name
200 Jhon
300 Sean

I want to select records which are not in Table 2 BUT in Table 1. In this case record of Bill and if we found that there is record available, then set the value of On_Vacation as "Y" for all cases.

Empl_ID is primary key for all above tables.

Thanks for the help.

 

[PHV]

I want to select records which are not in Table 2 BUT in Table 1
One way:
SELECT *
FROM Table1
WHERE Empl_ID NOT IN (SELECT Empl_ID FROM Table2)
Another way:
SELECT Table1.*
FROM Table1 LEFT JOIN Table2 ON Table1.Empl_ID = Table2.Empl_ID
WHERE Table2.Empl_ID IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DB2Problem]

I still need to toggle the On_Vacation flag to "Y

 

[PHV]

UPDATE Table1 SET On_Vacation = 'Y'
WHERE Empl_ID NOT IN (SELECT Empl_ID FROM Table2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244