Help with SQL Query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with SQL Query

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-01-26 : 03:54:30

I have having trouble with the following query. Basically what I want to do is set the IsChanged field to 1 where the PScale is changing FROM or TO A, B or C. Note it is the 4th character of the PScale I am looking at.


UPDATE Typed
SET Typed.IsChanged = 1,
FROM dbo.t_Typed as Typed
INNER JOIN dbo.t_Employ emp ON emp.PayN = Typed.PayN
INNER JOIN dbo.t_EmploySP esp ON esp.EmployId = emp.Id
WHERE ((ISNULL(esp.PScale,'') != '' AND ISNULL(Typed.PScale,'') != '') and (ISNULL(esp.PScale,'') <> ISNULL(Typed.PScale,''))) 
AND (Substring(esp.PScale,4,1) IN ('A','B','C') OR Substring(Typed.PScale,4,1) IN ('A','B','C'))

For Example, The following should set IsChanged to 1:

C ----> L

K ----> A

The following should NOT:
A ----> A

C ----> C

G -----> D

A ----> B

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 04:04:13

you need to do this inside trigger for update.

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-01-26 : 04:17:24

Is it not possible to write a query at all for this without using triggers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 04:28:02

quote:
Originally posted by Looper

Is it not possible to write a query at all for this without using triggers

its possible. but what you wanted is for this to happen following update thats why i told to write update trigger for that

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-01-26 : 04:41:44

The problem is I only want this query to run for a certain application, other applications use these tables also. So the update is only to occur when one particular app is running.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 04:43:01

quote:
Originally posted by Looper

The problem is I only want this query to run for a certain application, other applications use these tables also. So the update is only to occur when one particular app is running.

ok in that case wrap this logic also in procedure which does initial update

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources