Re: What's the MySQL syntax for this query?
> 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
Re: What's the MySQL syntax for this query?
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 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?
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?
@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 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?
> 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?
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?
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?
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 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
What's the MySQL syntax for this query?
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
