sql server new users: hallo

i got this message:

The column prefix 'r' does not match with a table name or alias name
used in the query

this is the sql query:

UPDATE VNAggr.DBA.resTable
SET resdeactivated = r.resid, endtime = getdate()
WHERE r.resid in (SELECT distinct d.devName, i1.ifindex, r.resid
FROM VNAggr.DBA.resTable r, VNAggr.DBA.configTable c1,
VNPoller.DBA.uifSttsUTMStats s, VNAggr.DBA.IfTable i1,
VNAggr.DBA.IfTable i2, VNAggr.DBA.configTable c2, VNAggr.DBA.devTable
d
WHERE i1.IFID = c1.IFID and r.resID = s.resID and c2.configID =
r.configID and
i2.IFID = c2.IFID and i1.ifIndex = i2.ifIndex and i1.devID = i2.devID
and
getdate() - s.Dttm <= '1900-01-01 00:06:00.000' and d.devID = i2.devID
and
r.resID not in (select distinct s.resID
FROM VNAggr.DBA.devTable d, VNAggr.DBA.resTable r,
VNPoller.DBA.uifSttsUTMStats s
,VNAggr.DBA.configTable c1, VNAggr.DBA.IfTable i1,
VNAggr.DBA.configTable c2, VNAggr.DBA.IfTable i2
WHERE (c1.auxStr like '%x[0-9,A-F][0-9,A-F]x%' or i1.ifName like
'%x[0-9,A-F][0-9,A-F]x%' or i1.ifDescr like '%Physical%') and
i1.IFID = c1.IFID and r.resID = s.resID and c2.configID = r.configID
and
i2.IFID = c2.IFID and i1.ifIndex = i2.ifIndex and i1.devID = i2.devID
and
getdate() - s.Dttm <= '1900-01-01 00:06:00.000' and d.devID =
i2.devID) and r.resid = 435865)

thank for help

ylvie,

Everything in your query after "the r.resid in" is inside a pair of quotes
( ), so it is one complex derived table. All of your aliases are _inside_
the derived table and not exposed outside. If you want to call the derived
table 'r' then you should add 'AS r' at the end of the statement.

If that is the case, however, then you are comparing r.resid with the
subselect/derived tables which would now be aliased as 'r'. So you probably
need to compare with something else.

This should all be simplified if possible.

RLF
[quoted text, click to view]

ylvie,

Here is a simplified view of your current code. I see that currently it is
not using a derived table, but a subselect:

UPDATE VNAggr.DBA.resTable
SET resdeactivated = r.resid, endtime = getdate()
WHERE r.resid in (everything else is a subselect)

Here you can see that there is no reference to the 'r' alias. Actually, you
do have an r alias, but it is strictly internal to the subselect. Therefore
your outer query cannot reference that alias.

You can see that it would not make sense to compare the results of the
subselect with the contents of something in the subselect. Also, your
subselect returns many columns, which is not allowed. If you fixed the 'r'
problem, you would next get:
Msg 116, Level 16, State 1, Line 1 Only one expression can be specified in
the select list when the subquery is not introduced with EXISTS.

This probably needs to be recast to use a join to one of the tables, or else
a derived table. Using a subselect it may be something like:

UPDATE VNAggr.DBA.resTable
SET resdeactivated = resid, endtime = getdate()
FROM VNAggr.DBA.resTable JOIN
WHERE resid in (SELECT resid from ... the other stuff in the subselect)

I have to admit that I have not tried to fully understand the query, because
I have no metadata, no sample data, and an unclear idea of what this update
should be doing. Hint: You really should use ANSI standard joins instead of
the old style of controlling the join in the WHERE clause. The Books Online
explain this in detail.

So, my best suggestion is still: Simplify the code and work through it one
piece at a time.

RLF

[quoted text, click to view]

[quoted text, click to view]

Thank you, but
I tried with 'AS r', but it doesn't work, whe is the 'end of
statement ?' to place th 'AS'

Everything Russell is telling you true. The problem is that the IN clause
only returns a list to evaluate a True/False conditions. Nothing from
within that query can be used to update the table.

The first thing I would do is properly format the statement so it can be
read correctly. That is the main purpose of formatting; it gives you the
ability to easily read, and therefore, easily debug error messages.

The next thing I would do is simply the statement, of which is to use proper
ANSI syntax.

When you are done, the query should look something like:

UPDATE table
SET
attributes = values

FROM
table JOIN other tables
ON join conditions

WHERE
filter conditions


You should be able to see the problem more clearly by properly formatting:


UPDATE VNAggr.DBA.resTable
SET
resdeactivated = r.resid
,endtime = GETDATE()

WHERE
r.resid IN
(
SELECT DISTINCT
d.devName
,i1.ifindex
,r.resid

FROM
VNAggr.DBA.resTable AS r
,VNAggr.DBA.configTable AS c1
,VNPoller.DBA.uifSttsUTMStats AS s
,VNAggr.DBA.IfTable AS i1
,VNAggr.DBA.IfTable AS i2
,VNAggr.DBA.configTable AS c2
,VNAggr.DBA.devTable AS d

WHERE
i1.IFID = c1.IFID
AND r.resID = s.resID
AND c2.configID = r.configID
AND i2.IFID = c2.IFID
AND i1.ifIndex = i2.ifIndex
AND i1.devID = i2.devID
AND GETDATE() - s.Dttm <= '1900-01-01 00:06:00.000'
AND d.devID = i2.devID

AND r.resID NOT IN
(
SELECT DISTINCT
s.resID

FROM
VNAggr.DBA.devTable AS d
,VNAggr.DBA.resTable AS r
,VNPoller.DBA.uifSttsUTMStats AS s
,VNAggr.DBA.configTable AS c1
,VNAggr.DBA.IfTable AS i1
,VNAggr.DBA.configTable AS c2
,VNAggr.DBA.IfTable AS i2

WHERE
(
c1.auxStr LIKE
'%x[0-9,A-F][0-9,A-F]x%'
OR i1.ifName LIKE
'%x[0-9,A-F][0-9,A-F]x%'
OR i1.ifDescr LIKE '%Physical%'
)

AND i1.IFID = c1.IFID
AND r.resID = s.resID
AND c2.configID = r.configID
AND i2.IFID = c2.IFID
AND i1.ifIndex = i2.ifIndex
AND i1.devID = i2.devID
AND GETDATE() - s.Dttm <= '1900-01-01
00:06:00.000'
AND d.devID = i2.devID

)

AND r.resid = 435865

)




You can see that this statement does not look like the simplified example
above.

There were several ways to do this, but here is one, and I'm not completely
sure I've properly carried all of the aliases correctly, but it should give
you a better idea of what was going wrong with your original statement.


UPDATE rT WITH(UPDLOCK)
SET
rT.resdeactivated = r.resid
,rT.endtime = GETDATE()

FROM
VNAggr.DBA.resTable AS rT

INNER JOIN

(
SELECT DISTINCT
d1.devName
,i1.ifindex
,r1.resid

FROM
VNAggr.DBA.IfTable AS i1

INNER JOIN
VNAggr.DBA.configTable AS c1
ON i1.IFID = c1.IFID

INNER JOIN
VNAggr.DBA.IfTable AS i2

INNER JOIN
VNAggr.DBA.configTable AS c2

INNER JOIN
VNAggr.DBA.resTable AS r1

INNER JOIN
VNPoller.DBA.uifSttsUTMStats AS s1
ON r1.resID = s1.resID

ON c2.configID = r1.configID

ON i2.IFID = c2.IFID

INNER JOIN
VNAggr.DBA.devTable AS d1
ON i2.devID = d1.devID

ON i1.ifIndex = i2.ifIndex
AND i1.devID = i2.devID

WHERE
GETDATE() - s1.Dttm <= '1900-01-01 00:06:00.000'

) AS r

WHERE
rT.resid = 435865

AND r.resid NOT IN
(
SELECT DISTINCT
s2.resID

FROM
VNAggr.DBA.IfTable AS i3

INNER JOIN
VNAggr.DBA.configTable AS c3
ON i3.IFID = c3.IFID

INNER JOIN
VNAggr.DBA.IfTable AS i4

INNER JOIN
VNAggr.DBA.configTable AS c4

INNER JOIN
VNAggr.DBA.resTable AS r2

INNER JOIN
VNPoller.DBA.uifSttsUTMStats AS s2
ON r2.resID = s2.resID

ON c4.configID = r2.configID

ON i4.IFID = c4.IFID

INNER JOIN
VNAggr.DBA.devTable AS d2
ON i4.devID = d2.devID

ON i3.ifIndex = i4.ifIndex
AND i3.devID = i4.devID

WHERE
(
c3.auxStr LIKE '%x[0-9,A-F][0-9,A-F]x%'
OR i3.ifName LIKE '%x[0-9,A-F][0-9,A-F]x%'
OR i3.ifDescr LIKE '%Physical%'
)

AND GETDATE() - s2.Dttm <= '1900-01-01 00:06:00.000'

)


The biggest thing was to turn the large SELECT statement into a derived
table so you could join it to the table you wanted to update.

Next, I am not a big fan of IN() clauses. I'd rather use a JOIN (if I need
to SELECT the data) or an EXISTS() clause (whenever I do not need the data,