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 <[email protected]> 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
