UPDATE & Job Scheduling
- Newbie using SQL 2000 - First Job Schedule is failing - Need help. When I run
this UPDATE query from the Table query pane I get this error message and when
I click OK the query runs just fine. And the error doesn't come up again
while I am in that session, only the first time I try to run the update.
Error
"The Query Designer does not support the Optional FROM clause SQL construct."
I want to schedule this, so in my only step in Job Scheduler I use the same
query but it always fails. Is it because of the one time error? If so how do
I get around that, and if not what am I doing wrong?
Thanks in advance for your help. See UPDATE statement below
UPDATE [dbo].[CM]
SET Manager = Team
FROM [National Call Stats], CM
WHERE [Rep Ssn] = AssocID AND [Date] = CallDate AND (CallDate >= DATEADD
(mm, DATEDIFF(mm, 0, GETDATE()), 0))
-
try this and try to use ANSI JOIN method. What is the relationship between the [National Call Stats] and [CM] table ?
UPDATE U
SET Manager = Team
from [National Call Stats], dbo.[CM] U
where [Rep Ssn] = AssocID
and [Date] = CallDate
and CallDate >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
-
Thanks KH for getting back to me. The relationship between the two tables updates the CM table Team with the manager name from the National Call Stats table where the rep ssn is equal to the associd and the dates between the two tables are equal. I am new at this, so what does the U do?
-
The U creates an alias, so you say
from MyTable a
and then you can use "a" in other parts of the statement. So
select a.col1, b.col2
from mytable a
inner join myothertable b
on a.pkcol = b.fkcol
where a.col1 > 0
In the update statement you can
update a
set x = y
from Mytable a
where a.x is null
Aliases make it easier to read and digest a long statement rather than having so many long table names. I often use alias that relate to teh table, so "Products" becomes p, "ProductDetails", pd, etc.
-
Unfortunately my Job is still not executing
-
Mark,
Have you tried running the query using "query analyzer" instead of in enterprise manager?
If you have a job, you should also check if the account of sql agent has sufficient permissions on the table.
UPDATE CM
SET Manager = Team
FROM [National Call Stats] NCS
INNER JOIN dbo.CM CM
ON [Rep Ssn] = AssocID AND [Date] = CallDate AND (CallDate >= DATEADD (mm, DATEDIFF(mm, 0, GETDATE()), 0))
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply