Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

just a real quick check on my update

Posted on 2011-02-17
11
Medium Priority
?
395 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:dbaSQL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
11 Comments
 
LVL 17

Author Comment

by:dbaSQL
ID: 34922504
basically, there is NO room for error, and i just want a double-check.  very simple update where exists, but it's always nice to have another set of eyes
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 34922634
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
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
*/

Open in new window


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
*/

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 34922717
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.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 22

Expert Comment

by:Thomasian
ID: 34922768
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
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
             )

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 34922839
>>>       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?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 34922868
>>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.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 34922877
ok, my bad again.

SELECT * FROM applicationtable WHERE LOANAPPLICATIONID = 46538827
SELECT * FROM loantable WHERE LOANAPPLICATIONID = 46538827
SELECT * FROM calltable WHERE loanid = 3188  

applicationtable.loanapplicationid = loantable.loanapplicationid
loantable.loanid = calltable.loanid

those three selects return the same family of data
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 2000 total points
ID: 34922901
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 = 'In-Process'
                       AND a.datefield < DATEADD(dd,-9,GETDATE())
                       AND l.loanid = c.loanid
             )

Open in new window

UPDATE l
SET status = 'Inactive' 
FROM loantable l INNER JOIN
     applicationtable a ON a.LOANAPPLICATIONID = l.LOANAPPLICATIONID
WHERE l.status = 'In-Process'
      AND a.datefield < DATEADD(dd,-9,GETDATE())

Open in new window


Again, I strongly recommend that you create a temp table to test the query before using it on your actual data.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 34922916
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
             )

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 34922921
looks like we typed the same on the calltable update.  :-)
i will test a bit, back soon
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 34923366
Tested out well.  Thank you for taking a look.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question