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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy