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:

<span><span>UPDATE</span> <span>mytable</span> <br /><span>   SET</span> <span>filterdata</span> <span>=</span> <span>(</span><span>SELECT</span> <span>src</span><span>.</span><span>filterdata</span> <br />                       <span>FROM</span> <span>mytable</span> <span>src</span> <br />                      <span>WHERE</span> <span>src</span><span>.</span><span>keyid</span> <span>=</span> <span>5</span><span>)</span> <br /><span> WHERE</span> <span>keyid</span> <span>=</span> <span>1</span> </span>

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

Tech Tags: