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.