Article:		SQL Subqueries
Subject:		mySQL help
Date:		2001-11-07 03:10:30
From:		adunkey
Hi John, good article, could I ask you something? Say I have a mySQL web database with lots of stuff coming into it, I'm trying to separate this by the primary key 'id', so in my perl I have a command like: update table_name set item_name = 'blah' where id in(select max(id) from table_name); but it does'nt work, have you any ideas? thanks ian

Showing messages 1 through 1 of 1.

• mySQL help
  2001-11-24 14:26:17  the_newt [View]
  
  
  MySQL does not yet support subqueries/sub-selects, but they're working on it. Its just not a priority. There are ways around it though.
  
  Your question seems vague but I'll try to answer as best as I can. I'm assuming you want the last id in the table. MAX() is a summary function and isn't really what you want to use to get the last id in the table. It will work but the results can be pretty sketchy.
  
  UPDATE <table> set item_name='bar' WHERE id IN( SELECT MAX(id) FROM <table>)
  
  could be rewriten as
  
  UPDATE <table> set item_name='bar' WHERE id = LAST_INSERT_ID(table_name.id)
  
  I'm not sure why you would want to do this as record updates should always be updated against a primary key. You're asking for trouble if you don't. Both statements above will always update the last inserted record in the table and have a high possibility of messing up the records.
  
  Hope this helps and John please step in if I'm wrong.
  
  The Newt