UPDATE SET with INNER JOIN condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE SET with INNER JOIN condition

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-11-17 : 21:30:50

Dear Gurus,
I am writing an Online Leave Application System for my company

Two related Tables regarding my question:

1 Leaves
2. LeaveType
Table Structure as below:

CREATE TABLE [dbo].[Tbl_Leaves](
[LeaveID] [int] IDENTITY(1,1) NOT NULL,
[EmpID] [nvarchar](50),
[LeaveTypeID] [int] NULL,
[LeaveStatusID] [int] NULL,
)

'LeaveStatusID
'1 = Draft
'2 = Pending Approval

'3 = Approved
'4 = Rejected

CREATE TABLE [dbo].[Tbl_LeaveType](
[LeaveTypeID] [int] IDENTITY(1,1) NOT NULL,
[LeaveTypeCode] [nvarchar](3),
[LeaveTypeDesc] [nvarchar](50),
[LeaveRequireApproval] [bit] NULL
)

'LeaveTypeID
'1 = Annual Leave | LeaveRequireApproval = True

'2 = MED Medical Leave | LeaveRequireApproval = False

Assumption:

1. User will first create a Leave Application in draft (entered in tbl_Leaves, LeaveTypeID = 1 by default)

2. Normal leave (such as Annual Leave) requires approval from Superior. WHERE Tbl_LeaveType.LeaveRequireApproval = TRUE

3. Medical Leave, Hospitalisation, etc does not require approval. WHERE Tbl_LeaveType.LeaveRequireApproval = FALSE

4. When user submit the Leave Application, my system will check the type of leave the user is submitting:

* User may submit multiple Leave Application with various type of LeaveType (2 days of Medical Leave and 2 days of Annual Leave for example) - WHERE Tbl_Leaves.LeaveID IN (1, 2, 3, 4)

If LeaveTypeID IN(1, etc) (Annual Leave) THEN UPDATE Tbl_leaves.LeaveStatus = 2 (Pending Approval);
If LeaveTypeID IN(2, etc) (Medical Leave) THEN UPDATE Tbl_leaves.LeaveStatus = 3 (Approved);

5. Tbl_LeaveType.LeaveRequireApproval = TRUE|FALSE defines the type of Leave Application that may requires approval

Question:

How to use ONE query to update the Tble_leaves.LeaveStatusID (to 2 or 3) depending on Tbl_LeaveType.LeaveRequireApproval?

I only figured out this much as below, and I am stucked (SQL below may:

UPDATE Tbl_Leaves
SET

'If Tbl_LeaveType.LeaveRequireApproval = TRUE then LeaveStatusID = 2,
'If Tbl_LeaveType.LeaveRequireApproval = FALSE then LeaveStatusID = 3,

FROM Tbl_Leaves
INNER JOIN
Tbl_LeaveType ON Tbl_Leaves.LeaveTypeID = Tbl_LeaveType.LeaveTypeID
WHERE (Tbl_Leaves.LeaveID IN (11, 12, 13))

Please advise

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-11-17 : 22:21:32

I just figured it out as below.

UPDATE _Leaves
SET
StatusID = CASE WHEN _LeaveType.LeaveRequiresApproval = 1 THEN 2 ELSE 3 END

FROM _LeaveType INNER JOIN
_Leaves ON _LeaveType.LeaveTypeID = _Leaves.LeaveTypeID
WHERE _LeaveType.LeaveRequiresApproval = 0 AND _Leaves.ID IN (1,2,3)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources