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:
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.