What's the MySQL syntax for this query?

2010-11-01 Thread Dave Merrill 

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 to
do this, ideally something compatible with all three dbs (hah)?

Thanks,
Dave

~|
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:338713
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: What's the MySQL syntax for this query?

2010-11-01 Thread Dave Merrill 

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

Re: What's the MySQL syntax for this query?

2010-11-01 Thread Dave Merrill 

This more straight ahead version also works for Oracle:

UPDATE 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
   )

Still no ideas for MySQL.
Dave

~|
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:338715
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: What's the MySQL syntax for this query?

2010-11-01 Thread Azadi Saryev 

iirc, your last oracle syntax example should work in mysql as well.

Azadi

On 01/11/2010 21:36 , Dave Merrill wrote:
 This more straight ahead version also works for Oracle:

 UPDATE 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
 )

 Still no ideas for MySQL.
 Dave

 

~|
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:338716
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: What's the MySQL syntax for this query?

2010-11-01 Thread Leigh 

 This more straight ahead version also works for Oracle:

What error are you getting? I would expect that syntax to work with MySQL 5.


  

~|
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:338717
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: What's the MySQL syntax for this query?

2010-11-01 Thread Dave Merrill 

@Azadi and Leigh, the oracle syntax gives this on mysql: Error Code:
1093 You can't specify target table 'dfv1' for update in FROM clause.

Ended up with this for mysql:

UPDATE MyTable t1
LEFT OUTER JOIN t2 ON t2.SomeColumn = t1.SomeColumn
AND t2.SomeOtherColumn = t1.SomeOtherColumn
AND t2.YetAnotherColumn = 42
SET Foo = 1
WHERE Bar = 99
AND t2.SomeColumn IS NULL

Funky stuff.

Dave


On Mon, Nov 1, 2010 at 10:14 AM, Leigh cfsearch...@yahoo.com wrote:

 This more straight ahead version also works for Oracle:

 What error are you getting? I would expect that syntax to work with MySQL 5.

~|
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:338719
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: What's the MySQL syntax for this query?

2010-11-01 Thread Leigh 

Yes, putting the JOIN in the UPDATE should work for MySQL.

 1093 You can't specify target table 'dfv1' for update in
 FROM clause.

Though I am thinking the actual query was different than what you posted. 
Because I tried version #3 with MySQL 5 and it _seemed_ to run without error. 
Anyway, glad you have something that works now.

-Leigh


  

~|
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:338720
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: What's the MySQL syntax for this query?

2010-11-01 Thread Dave Merrill 

Yes, the actual query's different, that's why the msg about 'dfv1',
which wasn't actually in the version I posted.

Anyway, I'm good I think, thanks for checking.

Dave

On Mon, Nov 1, 2010 at 11:42 AM, Leigh cfsearch...@yahoo.com wrote:

 Yes, putting the JOIN in the UPDATE should work for MySQL.

 1093 You can't specify target table 'dfv1' for update in
 FROM clause.

 Though I am thinking the actual query was different than what you posted. 
 Because I tried version #3 with MySQL 5 and it _seemed_ to run without error. 
 Anyway, glad you have something that works now.

 -Leigh




 

~|
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:338721
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: What's the MySQL syntax for this query?

2010-11-01 Thread Leigh 

 that's why the msg about 'dfv1',

Not just the alias. The FROM part sounded suspicious. This is the one that 
worked for me with MySQL:
http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:62159#338715

... but I am officially beating a dead horse now ;-) So back to work I go.

Later
-Leigh



  

~|
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:338722
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm