How do I do this update with Query Analyzer (I''m a web guy)
-
Let me start by saying that I am a web guy, not a SQL guy. I have never done large scale data updates directly through Query Analyzer, but felt this was the best way to accomplish what I need and thought it would be a good place to learn.
I need to update a single field ('termination date') in one table ('customers') about 1500 records with a date ('activitydate') that is pulled from another table ('activities').
As a web guy, Coldfusion to be exact, I am used to the syntax for loops within that language, however I can't seem to get a handle on how to do this directly in QA. I am very used to what I need to reference in my coldfusion code loop, but I am stuck with the code to do directly in QA. Any help is greatly appreciated. Here is some base code if I were to do it in Coldfusion. I am sure anyone can follow the logic.
SELECT CancelDate
FROM Activities INNER JOIN Customers
ON Customers.CustomerID=Activities.CustomerID
WHERE Customers.FormerCustomer=1
AND Activities.Activity LIKE 'canceled customer'
UPDATE Customers
SET TerminationDate=#CancelDate#
WHERE CustomerID=#CustomerID#
Any help is greatly appreciated. Obvioulsy, I could do this using my Coldfusion code, but I am looking to hone my SQL skills. Thanks for the great site.
-
Bob
You can perform a simple update statement that will handle your criteria like below:
update customers
set terminationdate = a.canceldate
from
customers c
inner join activities a
on c.customerid = a.customerid
where
c.formercustomer = 1
and a.activity = 'cancelled customer'
The only part I am unsure of is the "like" part in your code (which I made an equal). If the activity will not all equal 'cancelled customer' but starts out 'cancelled customer' you would change the query to read
a.activity like 'cancelled customer%'
Test the query on dev tables first to be sure but this will get you what you are looking for...
Chuck
-
Bob, Chuck is correct you want to do this as a set based operation not as a Loop.
As a CF dev, I know you're used to looping over another query or even doing QoQ if you're using MX or higher and that type of thing. Even using CF I'd still use a query like the one that Chuck did. It will be much more efficient and can be executed in one <cfquery> tag as such...
<CFQUERY NAME="Myqry" Datasource="MyDate" etc etc>
update customers
set terminationdate = a.canceldate
from
customers c
inner join activities a
on c.customerid = a.customerid
where
c.formercustomer = 1
and a.activity LIKE 'cancelled customer'
</cfquery>
Depending on how much data is in your table and how you have your indicies set up you may also want to take a look getting rid of the Like and finding a way to use = or IN. Remember if you need to query by %data% you typically will lose the ability to use an index. Like AND % work exaclty the same in TSQL no matter where you're callign them from.
-
Thanks a lot guys. I have to get my head out of the loop mentality. It is hard to since I have been working in that mode for few years.
Thank you for the additional advice regarding the more efficient uses with my data.
Bob
-
This thread may be a bit stale, but let me toss in another $.02 worth, if I may ...
One trick I've found is helpful for doing updates via Query Analyzer is to first write a query to select (only select) the rows you want to change. Other than using SELECT rather than UPDATE, make it the same as the update query you're considering ... same joins, same WHERE clauses, etc.
Then, when you're sure that the SELECT query only brings up the rows that are supposed to be changed, change the query to an UPDATE and proceed.
YMMV, of course, but I've found that approach to be helpful.
-
Thanks for the addition Geoffrey. I found myself doing just that thing today.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply