SQL 2005 Update Table with Where Exists Issue

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

SQL 2005 Update Table with Where Exists Issue

Author

Topic

EoinOReilly
Starting Member

2 Posts

Posted - 2012-09-06 : 10:50:56

Hi,

I have 4 tables A, B, C and D

I am using a SP to do a number of things

1. insert records into table B using a join from A,B and C
2. updating a flag in A to say it's inserted in B
3. select all entries from B

1.

insert into B
select ID
from A inner join C on A.ID = C.ID
inner join D on A.ID = D.ID
where A.Customer = 'XYZ'

This might insert 200 records

2.
Now I am trying to update a flag in A using the same criteria and the where exists syntax

update A
set Flag = 1
where exists (select ID from C on A.ID = C.ID
inner join D on A.ID = D.ID
where A.Customer = 'XYZ')

Sometimes this updates all 200 records BUT most of the time it only updates 1 record which means the next tine the SP is run, table B will contain duplicates.

I have restored the database into my test environment and the SP always runs correctly. Could this be a configuration issue?

The SP still completes and outputs the values from Table B so it is not crashing.

Any ideas?

Eoin O'Reilly

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-06 : 11:47:57

Your UPDATE statement should be more like this:

UPDATE a SET flag = 1
WHERE EXISTS
(
	SELECT * FROM c INNER JOIN d ON d.id = c.id
	WHERE a.Id = c.id
)

Another alternative, which would update A and insert into B in on e statement is as follows:

UPDATE A SET flag = 1
OUTPUT A.ID INTO B
FROM
	A
       INNER JOIN C
            ON  A.ID = C.ID
       INNER JOIN D
            ON  A.ID = D.ID
WHERE  A.Customer = 'XYZ'

EoinOReilly
Starting Member

2 Posts

Posted - 2012-09-06 : 12:14:49

Hi,

The actual update statement is

UPDATE SEARCH_REQUEST
SET SENT_TO_FEES = 1,
SENT_TO_FEES_DATE = @SYS_DATE
WHERE EXISTS (
SELECT SEARCH_DETAILS_ID
FROM CUSTOMER_ACCOUNT CA,
STATEMENT_ORDER_QUEUE SQ,
FFF015_FEE F015
WHERE CUSTOMER_CHARGE_ACCOUNT_ID = CA.ACCOUNT_ID
AND SEARCH_REQUEST.SEARCH_DETAILS_ID = F015.SEARCH_REQUEST_ID
AND SEARCH_DETAILS_ID = SQ.SEARCH_REQUEST_ID
AND (CASH_PAYMENT = 0
AND SWITCHED_ACCOUNT = 0
AND CA.EXEMPT = 0)
AND RTRIM(LTRIM(UPPER(CA.CUSTOMER_TYPE))) IN (SELECT RTRIM(LTRIM(UPPER(ACCOUNT_TYPE))) FROM ACCOUNT_TYPE WHERE BUSINESS_ACCOUNT = 1)
AND RTRIM(LTRIM(UPPER(REQUEST_CUSTOMER_TYPE))) IN (SELECT RTRIM(LTRIM(UPPER(ACCOUNT_TYPE))) FROM ACCOUNT_TYPE WHERE BUSINESS_ACCOUNT = 1)
AND SENT_TO_FEES = 0
)

Should I be selecting * instead of the SEARCH_DETAILS_ID. Just think it's weird that sometimes it works.

Eoin O'Reilly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources