The programming solution is work out the column name in your script, ie do "describe tablename" in your script, look for the column name marked as "PRI" in the key column, then insert this column name in the select statement.
Andy > -----Original Message----- > From: Rhino [mailto:[EMAIL PROTECTED] > Sent: 07 October 2004 14:08 > To: Paul Hanlon; [EMAIL PROTECTED] > Subject: Re: Referring to columns by ordinal > > > ----- Original Message ----- > From: "Paul Hanlon" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, October 07, 2004 4:55 AM > Subject: Referring to columns by ordinal > > > > Hi all, > > I've searched all over the place for a solution to this, and I hope you > can > > help. I'm trying to write a very simple database handling script. The > SQL > > statement I'm trying to do is SELECT * FROM tablename WHERE > the_first_column > > = a_number; > > All my tables have the primary key in the first column, but they are > called > > different names. What can I use to replace the_first_column. > > > Sorry, I don't think there is any way to use ordinals in a WHERE clause. > You > will have to use the actual name of the first column. > > According to the manual, > http://dev.mysql.com/doc/mysql/en/Problems_with_alias.html, you can use an > alias to refer to a column in GROUP BY, ORDER BY, or HAVING but *not* in > WHERE. The reason: "This is because when the WHERE code is executed, the > column value may not yet be determined." > > Now, this refers to an alias which you have defined in a SELECT clause, > like > 'Select count(*) as num", not an ordinal. I don't see anything that > explicitly says that you can't have an ordinal in a WHERE so there is > always > the possibility that an ordinal is valid in a WHERE. > > However, I think you can disprove that possibility very quickly by trying > a > query like: > > select * > from mytable > where 1 = 'Jones'; > > This assumes that the first column of your table contains surnames. > > I think you'll see that this doesn't work. > > There's at least one very practical reason why it would be very confusing > if > ordinals were allowed in WHERE clauses. Suppose you had a table that > contained integers in the some columns and you used an ordinal to > represent > the column position instead of using its name. Consider this query: > > select * > from mytable > where 2 = 7; > > Is this query trying to find all the rows where the value in the second > column is 7 or all the rows where the value in the 7th column is 2? The > only > way to prevent a misinterpretation here is to insist that the integer to > the > left of the equal sign is always a column ordinal and the integer to the > right of the equal sign is always a literal. > > The only solution I can see for your problem would be to re-create all of > your tables and this time name the first column of each table something > like > 'key' or 'primary key'. Then, all your queries could say something like: > > select * > from mytable > where key = 7; > > Rhino > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
