SQL Update query (error message)

Neal · August 3rd, 2007, 05:04 AM

Hi,

I have a problem when executing the following SQL 2000 query (Query Analyser). The field is updated (a single row table) when executed, however, there is an associated error message which I don't want, as this is to become a step within an automated DTS package:

Code:

UPDATE dbo.Update_Status 
SET dbo.Update_Status.CurStatus = '19'
SELECT dbo.Mer_Sales.Division_Reference, 
dbo.Update_Status.CurStatus
FROM 
dbo.Mer_Sales.Division_Reference, 
dbo.Update_Status.CurStatus
WHERE (((dbo.Update_Status.CurStatus)='18') AND 
((dbo.Mer_Sales.Division_Reference)="1"));

Despite the error message below, the fields do exist. What am I doing wrong?

Output:
(1 row(s) affected)

Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'dbo.Mer_Sales.Division_Reference'.
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'dbo.Update_Status.CurStatus'.

A further inconsistency is that the '1 row(s) affected' occurs when I run the update query for a 2nd, 3rd time etc.

ONLY as a last resort, maybe I can trap for the error message (but how do I do this?).

Thanks in advance,

Neal

A Northern Soul

Jeff Moden · August 3rd, 2007, 08:57 PM

First, you're missing a join condition in the WHERE clause... you've created a partial cross-join because you're missing a relationship between the two tables... you need something that says...

AND dbo.Update_Status.somecolumn = dbo.Mer_Sales.somecolumn

Also, you really need to read about the UPDATE statement in Books Online and look at some of the examples there... what you need to end up with is something that looks more like this...

UPDATE us
SET CurStatus = '19'
FROM dbo.Mer_Sales ms,
dbo.Update_Status us
WHERE us.CurStatus='18'
AND ms.Division_Reference='1'
AND you_are_missing_a_join_condition_to_relate_the_two _tables

Keywords to study in Books Online

JOINS
SELECT
TABLE ALIAS
UPDATE
FROM

--Jeff Moden