Dang it, I was afraid I'd mess up that conversion. The SQL Server version is actually this (2nd and 3rd lines switched):
UPDATE t1 SET Foo = 1 FROM MyTable t1 WHERE Bar = 99 AND NOT EXISTS ( SELECT * FROM MyOtherTable t2 WHERE t2.SomeColumn = t1.SomeColumn AND t2.SomeOtherColumn = t1.SomeOtherColumn AND t2.YetAnotherColumn = 42 ) Dave On Mon, Nov 1, 2010 at 9:10 AM, Dave Merrill <enigm...@gmail.com> wrote: > The SQL Server version of this query is this: > > UPDATE t1 > FROM MyTable t1 > SET Foo = 1 > WHERE Bar = 99 > AND NOT EXISTS > ( > SELECT * > FROM MyOtherTable t2 > WHERE t2.SomeColumn = t1.SomeColumn > AND t2.SomeOtherColumn = t1.SomeOtherColumn > AND t2.YetAnotherColumn = 42 > ) > > > The Oracle version is this: > > UPDATE > ( > SELECT * > FROM MyTable t1 > WHERE Bar = 99 > AND NOT EXISTS > ( > SELECT * > FROM MyOtherTable t2 > WHERE t2.SomeColumn = t1.SomeColumn > AND t2.SomeOtherColumn = t1.SomeOtherColumn > AND t2.YetAnotherColumn = 42 > ) > ) join_table > SET join_table.Foo = 1 > > > (Hopefully I didn't mess things up converting from real names and > simplifying a bit. Sorry about the bent indentation too; try it in a > monospace font.) > > What's the MySQL version? Neither of those. All the examples on the > net are simple joins, not like this. Any other more reasonable ways ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338714 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm