Aliases in Update
Posted by patterj (patterj), 9 June 2005It took me a week to figure this out, so I'd figured I'd share the knowledge. When you use an alias, you normally use it in your FROM clause, but since you don't have a FROM clause in an update, you have to state your alias in your update clause.
UPDATE table1, table2 as tbl2, tbl3 as tbl3
SET table1.field1 = tbl2.field1, table1.field3=tbl3.field3
WHERE tbl3.field4 = tbl2.field4
it's handy to do this when you are updating a table w/ data from another database, because MySQL makes you alias tables from another database. Hopes this helps someone out.
Posted by admin (Graham Ellis), 10 June 2005Post much appreciated, thanks.
Always grateful for relevanty tips, and they're much wider read than you might realise.
If you're reading this thread in the "live forum" which is accessible through http://www.wellho.net/forum/index.html, you may see just a few "hits" recorded. The real volume readers come via the archive, at http://www.wellho.net/forum/top.html which is genarated from each thread with a lag of 24 to 48 hours, and manually re-indexed as approproiate on a weekly cycle - I've just looked at the logs and 463 visitors arrived somewhere on this site in the last 24 hour period via web searches that include the word "Mysql".
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: firstname.lastname@example.org • WEB: http://www.wellho.net • SKYPE: wellho