This is the problem when using UPDATE with a JOIN.

You have to ensure there is only a one to one relationship between the two tables. If there is a one...

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER dbo.TI_TransactionTable

ON dbo.TransactionTable

AFTER INSERT

AS

SET NOCOUNT ON

UPDATE dbo.TargetTable

SET StatusFlag = ' '

WHERE StatusFlag <> ' '

AND EXISTS

(

SELECT *

FROM inserted I

WHERE I.LotJob = dbo.TargetTable.LotNumber

AND I.TrnQuantity = dbo.TargetTable.ReceiptQty

AND...

-- either (LEN ignores trailing spaces)

LEFT(vm.VCTCollectZipcodeId, LEN(vhml.ZipcodeFrom)) = vhml.ZipcodeFrom

-- or

vm.VCTCollectZipcodeId LIKE vhml.ZipcodeFrom + '%'

There are some MDX articles in the Stairways menu, above Forums, on SSC.

Obviously you have to use the PATINDEX to do the SUBSTRING:

-- *** Test Data ***

CREATE TABLE #t

(

Grade varchar(8000) NULL

)

INSERT INTO #t

SELECT 'R1 - Background, M0 - No Maculopathy'

UNION ALL SELECT...

DELETE dbo.Table_2

WHERE EXISTS

(

SELECT *

FROM dbo.Table_1 T1

WHERE T1.[UNIQUE #] = Table_2.[UNIQUE #]

AND T1.[SEQ ORDER #] = Table_2.[SEQ ORDER #]

)

We run separate instances for thrid party products where they insist that they need sysadmin rights to the instance in order to provide support.

We tend to run all our internally...

;WITH Grp

AS

(

SELECT *

,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY trans_time)

- ROW_NUMBER() OVER (PARTITION BY user_id, trans_type ORDER BY trans_time) AS Grp

FROM dbo.user_trans

)

,GrpNoDups

AS

(

SELECT user_id, trans_type, grp

,MIN(trans_time) AS trans_time

FROM Grp

GROUP BY user_id,...

I suspect SalesHeader has a foreign key to Contact. Try:

DELETE SalesHeader WHERE ContactId = 10

DELETE Contact WHERE ContactId = 10

-- *** Test Data ***

CREATE TABLE #t

(

Col1 char(1) NOT NULL

,Col2 int NOT NULL

)

INSERT INTO #t

SELECT 'A', 1

UNION ALL SELECT 'A', 2

UNION ALL SELECT 'A', 3

UNION ALL SELECT 'B', 4

UNION ALL...

I cannot remember if SQL2000 gave a specific exception if you tried to restore a backup produced by a more recent version of SQL server.

It may be worth finding out...

As sometimes excessive data validation can cause users not to enter anything, another approach is to produce regular data quality reports grouped by department, user etc.

If management want information based...

The following will order the compsite alphabetically but it will have an unique value:

;WITH Composites

AS

(

SELECT A.ColA, A.ColB

, STUFF(

(SELECT ' ' + CAST(B.ColC as varchar(255)) + ','

FROM YourTable B

...

In Theory, it makes no difference what you SELECT in an EXISTS sub-query as it is just syntactical sugar. (ie Nothing is actually selected.)

I seem to remember someone doing a...

You need to give the columns in a derived table a name so do something like:

SELECT COUNT(*)

FROM

(

SELECT UPPER(s_name) AS s_name

FROM bmi_tr

) dual;

or:

SELECT COUNT(*)

FROM

(

SELECT UPPER(s_name)

FROM bmi_tr

) dual (s_name)

Of course, you will...