August 28, 2008 at 11:50 pm
I have an update query that works just fine...but could be better?
It uses IN and GROUP BY to verify a key is unique, then updates on that condition. I'd like to streamline the query using a SELF JOIN instead.
Current Query:
update table1
set field1=newid()
where key1 in
(
select key1
from table1
where field2 <> 'U'
group by key1
having count(*)=1
)
Attempted Revision:
update table1
set field1=newid()
from table1 v1
left join table1 v2
on v1.key1=v2.key1
and v1.importGUID<>v2.importGUID
where v2.importGUID IS NULL
and v1.field2<>'U'
The revision works perfectly as a SELECT query, but when attempting UPDATE the error is:
Msg 8154, Level 16, State 1, Line 1
The table 'table1' is ambiguous.
Thanks, Chris
**ALL SORTED NOW!
Just needed to be 'update v1' and it works fine...
August 29, 2008 at 8:09 am
Your update statement needs to reference the appropriate table alias rather than the actual table name. Once you alias the table in the from clause (which I almost always do) you do not use the base table name anywhere else in the query.
So just change the first line to
Update v1
August 29, 2008 at 8:24 am
Thanks Ray!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply