dbaSQL
asked on
just a real quick check on my update
UPDATE dbo.tableA
SET status = 'InActive'
WHERE EXISTS(SELECT 1 FROM dbo.tableB b JOIN dbo.tableA a
ON a.loanID = b.loanID
AND a.status = 'In-Process'
WHERE b.datefield < DATEADD(dd,-9,GETDATE()))
sorry, i just have to be certain
SET status = 'InActive'
WHERE EXISTS(SELECT 1 FROM dbo.tableB b JOIN dbo.tableA a
ON a.loanID = b.loanID
AND a.status = 'In-Process'
WHERE b.datefield < DATEADD(dd,-9,GETDATE()))
sorry, i just have to be certain
That will depend on what you are trying to do. The update query you posted will update all the records in tableA when the where condition is satisfied. If you have no room for error, I recommend that you create some test data to test your query first before using it on your actual table.
Here's the result of your query with some test data
Here's a modified query which will only update records with the same loanID
Here's the result of your query with some test data
DECLARE @tableA table (loanID int, status varchar(100))
DECLARE @tableB table (loanID int, datefield datetime)
INSERT INTO @tableA
SELECT 1,'In-Process'
UNION ALL SELECT 1,'Active'
UNION ALL SELECT 2, 'Active'
INSERT INTO @tableB
SELECT 1, GETDATE()-10
UNION ALL SELECT 2, GETDATE()-10
UPDATE @tableA
SET status = 'InActive'
WHERE EXISTS(SELECT 1 FROM @tableB b JOIN @tableA a
ON a.loanID = b.loanID
AND a.status = 'In-Process'
WHERE b.datefield < DATEADD(dd,-9,GETDATE()))
SELECT * FROM @tableA
/*
loanID status
1 InActive
1 InActive
2 InActive
*/
Here's a modified query which will only update records with the same loanID
DECLARE @tableA table (loanID int, status varchar(100))
DECLARE @tableB table (loanID int, datefield datetime)
INSERT INTO @tableA
SELECT 1,'In-Process'
UNION ALL SELECT 1,'Active'
UNION ALL SELECT 2, 'Active'
INSERT INTO @tableB
SELECT 1, GETDATE()-10
UNION ALL SELECT 2, GETDATE()-10
UPDATE T
SET status = 'InActive'
FROM @tableA T
WHERE EXISTS(SELECT 1 FROM @tableB b JOIN @tableA a
ON a.loanID = b.loanID
AND a.status = 'In-Process'
WHERE b.datefield < DATEADD(dd,-9,GETDATE()) AND a.loanID=T.loanid)
SELECT * FROM @tableA
/*
loanID status
1 InActive
1 InActive
2 Active
*/
ASKER
I'm sorry, I really should have provided better detail. These are the two updates I'm trying to replace. This code precedes me, and it has come to our attention that the update is incorrect because customerid should not be in the join. it is updating too much data, and it should be based on loanid, not customerid. the loantable and applicationtable are what i was referencing as 'tableA' and 'tableB', and they are joined by loanid, which is why i attempted my posted where exists.
update calltable set status = 'InActive' where customerid in
(select a.customerID
from applicationtable a join loantable l
on a.customerID = l.customer_ID
where l.status = 'InProcess'
and a.applicationDate < DATEADD(dd,-9,GETDATE())
update loantable set status = 'InActive' where customerid in
(select a.customerID
from applicationtable a join loantable l
on a.customerID = l.customerID
where l.status = 'InProcess'
and a.applicationDate < DATEADD(dd,-9,GETDATE())
i am wondering if my previous post is inadequate, as I am not accounting for the calltable. i am only looking at loan and application table joined on loanID.
the loantable can be joined to the calltable also by loanID
UPDATE calltable
SET status = 'InActive'
WHERE EXISTS(SELECT 1 FROM applicationtable a JOIN loantable l
ON a.loanID = l.loanID JOIN calltable c
ON l.loanid = c.loanid
WHERE l.status = 'In-Process'
AND a.datefield < DATEADD(dd,-9,GETDATE()))
I am unsure if that will effectively replace the customerID IN clause.
update calltable set status = 'InActive' where customerid in
(select a.customerID
from applicationtable a join loantable l
on a.customerID = l.customer_ID
where l.status = 'InProcess'
and a.applicationDate < DATEADD(dd,-9,GETDATE())
update loantable set status = 'InActive' where customerid in
(select a.customerID
from applicationtable a join loantable l
on a.customerID = l.customerID
where l.status = 'InProcess'
and a.applicationDate < DATEADD(dd,-9,GETDATE())
i am wondering if my previous post is inadequate, as I am not accounting for the calltable. i am only looking at loan and application table joined on loanID.
the loantable can be joined to the calltable also by loanID
UPDATE calltable
SET status = 'InActive'
WHERE EXISTS(SELECT 1 FROM applicationtable a JOIN loantable l
ON a.loanID = l.loanID JOIN calltable c
ON l.loanid = c.loanid
WHERE l.status = 'In-Process'
AND a.datefield < DATEADD(dd,-9,GETDATE()))
I am unsure if that will effectively replace the customerID IN clause.
In your original query, can't you just change customerid to loanid?
i.e
update calltable set status = 'InActive' where loanid in
(select l.loanID
from applicationtable a join loantable l
on a.customerID = l.customer_ID
where l.status = 'InProcess'
and a.applicationDate < DATEADD(dd,-9,GETDATE())
Regarding your last query, that will not work as you expected because the table being updated is not correlated to the subquery in you where condition. Which means that all the records in calltable will be updated even if only 1 record satisfies the condition.
Here's how it should look like
i.e
update calltable set status = 'InActive' where loanid in
(select l.loanID
from applicationtable a join loantable l
on a.customerID = l.customer_ID
where l.status = 'InProcess'
and a.applicationDate < DATEADD(dd,-9,GETDATE())
Regarding your last query, that will not work as you expected because the table being updated is not correlated to the subquery in you where condition. Which means that all the records in calltable will be updated even if only 1 record satisfies the condition.
Here's how it should look like
UPDATE c
SET status = 'InActive'
FROM calltable c
WHERE EXISTS (SELECT 1 FROM applicationtable a JOIN loantable l
ON a.loanID = l.loanID
WHERE l.status = 'In-Process'
AND a.datefield < DATEADD(dd,-9,GETDATE())
AND l.loanid = c.loanid
)
ASKER
>>> update calltable set status = 'InActive' where loanid in
I was really hoping to avoid the IN list, simply for performance reasons.
But the 'update c' is on the calltable, so that avoids the use of the IN list. What do you think of the second update, on the loantable?
I was really hoping to avoid the IN list, simply for performance reasons.
But the 'update c' is on the calltable, so that avoids the use of the IN list. What do you think of the second update, on the loantable?
ASKER
>>WHERE EXISTS (SELECT 1 FROM applicationtable a JOIN loantable l
ON a.loanID = l.loanID
The loanid is not in applicationtable. It is in the loantable and calltable.
ON a.loanID = l.loanID
The loanid is not in applicationtable. It is in the loantable and calltable.
ASKER
ok, my bad again.
SELECT * FROM applicationtable WHERE LOANAPPLICATIONID = 46538827
SELECT * FROM loantable WHERE LOANAPPLICATIONID = 46538827
SELECT * FROM calltable WHERE loanid = 3188
applicationtable.loanappli cationid = loantable.loanapplicationi d
loantable.loanid = calltable.loanid
those three selects return the same family of data
SELECT * FROM applicationtable WHERE LOANAPPLICATIONID = 46538827
SELECT * FROM loantable WHERE LOANAPPLICATIONID = 46538827
SELECT * FROM calltable WHERE loanid = 3188
applicationtable.loanappli
loantable.loanid = calltable.loanid
those three selects return the same family of data
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is the reference to c.loan_id within the subquery enough to control the dataset, rather than update all records in calltable?
UPDATE c
SET status = 'InActive'
FROM calltable c
WHERE EXISTS (SELECT 1 FROM applicationtable a JOIN loantable l
ON a.LOANAPPLICATIONID = l.LOANAPPLICATIONID
WHERE l.status = 'InProcess'
AND a.APPLICATIONDATE < DATEADD(dd,-9,GETDATE())
AND l.loan_id = c.loan_id
)
ASKER
looks like we typed the same on the calltable update. :-)
i will test a bit, back soon
i will test a bit, back soon
ASKER
Tested out well. Thank you for taking a look.
ASKER