SQL UPDATE from another row in the same table

My SQL skills are getting rusty.  I have some code where I needed to quickly copy a field from one row in a table to another row in the same table.  Bascially, a self-referencing UPDATE. I knew I could so it with a simple UPDATE statement, but I forgot the ANSI syntax.  Self-joining UDPATE statements can be a little squirrelly, so it's best to follow the ANSI standard.  So I went looking for the Master, Mr. Joe Celko.

I did a quick google through the newsgroups on celko update "sql Server", and I found what I was looking for on the third match.  If you really want to learn SQL as a language, Celko's books are very good.

This is what I ended up using:

UPDATE mytable
   SET filterdata = (SELECT src.filterdata
                       FROM mytable src
                      WHERE src.keyid = 5)
 WHERE keyid = 1

The table and field names have been changed to protect the innocent.

Tech Tags: Celko Update self+join SQL SQL+Server