Women in Technology

Hear us Roar



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
Full Threads Oldest First

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