Update select and compare 2 tables... 2

[RosieGp]

I have the following tables:
Employee
ID Name Job_Status
6789-1 Joe N
6789-2 Joe N
6789-3 Joe N

EmployeeQualification
ID Name Qualifications Acquired_Date
6789-1 Joe PA NULL
6789-1 Joe Secetary NULL
6789-1 Joe SalesMan 2009-10-10

What I have to do is write and update Statement which will update The Job_Status for all the above to Y when there is no NULL value in Acquired_Date column...

So only after Acquired_Date IS NOT NULL update Job_Status = 'Y. Therefore,
EmployeeQualification
ID Name Qualifications Acquired_Date
6789-1 Joe PA 2009-11-6
6789-1 Joe Secetary 2009-06-11
6789-1 Joe SalesMan 2009-10-10

then update the Job_Status for as following:
Employee
ID Name Job_Status
6789-1 Joe Y
6789-2 Joe Y
6789-3 Joe Y


My query is not working:

Update Employee
     SET [Job_Status] = 'Y'
     Where [ID] Like Left('ID', PatIndex('%-%', 'ID' + '-')-1) + '%'
     and exists (select 1
         from EmployeeQualification EQ
         where EQ.Acquired_Date IS NOT NULL
         and EQ.ID = ID)

Any help is much appreciated...
Thanks...

 

[markros]

Try

Update Employee
     SET [Job_Status] = 'Y'
     Where exists (select 1
         from EmployeeQualification EQ
         where EQ.Acquired_Date IS NOT NULL
         and EQ.ID = Employee.ID)

PluralSight Learning Library

 

[RosieGp]

markos,
It will only update the first row:
Employee
ID Name Job_Status
6789-1 Joe Y
6789-2 Joe N
6789-3 Joe N

I have to get all of them to change to 'Y'. So the result should be:
Employee
ID Name Job_Status
6789-1 Joe Y
6789-2 Joe Y
6789-3 Joe Y

I also tried the follwoing:
Update Employee
SET [Job_Status] = 'Y'
FROM Employee EMP, EmployeeQualification EQ
where EQ.Acquired_Date IS NOT NULL
and EQ.ID = EMP.ID
and EMP.ID LIKE '6789%'

this won't work correctly...
Ok May be i'm wrong somewhere...
Let me try to explain again...
Each Qualification is acquired on a different date... So after teh candidate acquire all 3 or 4 skills required for the job and acquired date is updated only then his status is changed for all the Ids for him to ' Y '.
So what I have to check is teh follwing :

Update Employee 
Case When EmployeeQualification.Acquired_Date IS NOT NULL for all Instances of EmployeeQualification.ID (3 in this case 6789-1,6789-1,6789-1 it could also be like 6789-1, 6789-2) then Job_Status = 'Y' all all instances of Employee.ID (6789-1,6789-2,6789-3)

else 

Case When EmployeeQualification.Acquired_Date IS NULL for any Instances of EmployeeQualification.ID (3 in this case) then Job_Status = 'N' for all instances of Employee.ID (6789-1,6789-2,6789-3)

I really need to make this work... Please help...

 

[markros]

I see now.

Try:

Update Employee
     SET [Job_Status] = 'Y'
     Where exists (select 1
         from EmployeeQualification EQ
         where EQ.Acquired_Date IS NOT NULL
         and EQ.ID = substring(Employee.ID,1,charindex('-',EmployeeID)) + '1')

PluralSight Learning Library

 

[RosieGp]

@markos,

Even if the Acquired_Date is NULL, it is still updating the Job_Status to 'Y', which should not be the case.
So if the data looks the following:
EmployeeQualification
ID Name Qualifications Acquired_Date
6789-1 Joe PA 2009-06-07
6789-1 Joe Secetary NULL
6789-1 Joe SalesMan 2009-10-10

In this case Acquired_Date is still NULL for Secetary. So it should not Update the Job_Status to 'Y' in Employee table. Only if all the Acquired_Date for all '6789%' in EmployeeQualification is not NULL, Job_Status should be updated to 'Y'.

Update Employee
   SET [Job_Status] = 'Y'
   FROM Employee Emp, EmployeeQualification EQ
   where EQ.Acquired_Date IS NOT NULL
   and EQ.ID = substring (Emp.ID,1,charindex('-',Emp.ID)) + '1')         
   and Emp.ID LIKE '6789%'

The above code will update [Job_Status] = 'Y' even if there is a NULL in Acquired_Date... But i'm able to update all instances for '6789%' like 6789-1, 6789-2, 6789-3 etc. to show 'Y'. the only issue now is to check for Acquired_Date IS NOT NULL for all instances of '6789%'.

Please see if I need to change or add more conditions...
Thanks...

 

[RosieGp]

Any help is much appreciated. Please see iF you can help...
How do I make this work...

 

[markros]

Are you using SQL Server 2005 or up? If so, you need to order the second table then (convert it to cte using row number).

Do you need the exact solution or this hint is enough?

PluralSight Learning Library

 

[RosieGp]

SQL Server 2008... Please if you can show me the exact query, I'm so confused and need this to work...
Please..

 

[Rhys666]

;WITH EmployeeQualification_CTE
(
	ID
)
AS
(
	SELECT
		ID
	FROM
		EmployeeQualification x
	WHERE
		NOT EXISTS (SELECT ID FROM EmployeeQualification y WHERE y.Acquired_Date IS NULL)
)
UPDATE
	emp
SET
	emp.Job_Status = 'Y'
FROM
	Employee emp
JOIN 
	EmployeeQualification_CTE cte ON SUBSTRING(emp.ID, 1, CHARINDEX('-', emp.ID)-1) = SUBSTRING(cte.ID, 1, CHARINDEX('-', cte.ID)-1);

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[RosieGp]

@Rhys
Thnaks a lot for quick reply. Ok I thing I understand this, But where do I put and Emp.ID LIKE '6789%'. Please advice

 

[RosieGp]

Ok I don't know why it is still not working. I created a store procedure

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE Job_StatusFlag 
      @EmployeeId varchar(10)
AS
;WITH EmployeeQualification_CTE
(
    ID
)
AS
(
    SELECT
        ID
    FROM
        EmployeeQualification x
    WHERE
        NOT EXISTS (SELECT ID FROM EmployeeQualification y WHERE y.Acquired_Date IS NULL)
)
UPDATE
    emp
SET
    emp.Job_Status = 'Y'
FROM
    Employee emp
JOIN 
    EmployeeQualification_CTE cte ON SUBSTRING(emp.ID, 1, CHARINDEX('-', emp.ID)-1) = SUBSTRING(cte.ID, 1, CHARINDEX('-', cte.ID)-1) and emp.ID like 'EmployeeId%';
GO

i need

and emp.ID like 'EmployeeId%'

this conditions as I only change the status of the ID that is entered by the user...

Now like in the following case, I still have NULL
EmployeeQualification
ID Name Qualifications Acquired_Date
6789-1 Joe PA 2009-06-07
6789-1 Joe Secetary NULL
6789-1 Joe SalesMan 2009-10-10

it is still updating The Job_Status to 'Y' like
ID Name Job_Status
6789-1 Joe Y
6789-2 Joe Y
6789-3 Joe Y
It should not be happening... Please see if you can help more...


Thnaks for your help guys....

 

[markros]

What is the field that guarantees the order in this

EmployeeQualification
ID Name Qualifications Acquired_Date
6789-1 Joe PA 2009-11-6
6789-1 Joe Secetary 2009-06-11
6789-1 Joe SalesMan 2009-10-10

?

Assuming there is an extra field, then

;with cte as (select *, substring(ID,1,charindex('-',ID)) + 
cast(row_number() over (partition by ID order by ExtraOrderField) as varchar(20)) as RowID from EmployeeQualifications)

update Employee set Status = 'Y' from Employee
inner join cte on Employee.ID = cte.RowID where
cte.Acquired_Date is NOT NULL

PluralSight Learning Library

 

[RosieGp]

Thanks really great great form... and great writers...