Updating record n rows before last inserted id

Databases
1

mySql + Coldfusion

I use LAST_INSERT_ID() AS newID to determine inserted record:


<cfquery name="getLastID" datasource="#arguments.dsn#">
    SELECT LAST_INSERT_ID() AS newID
    FROM products
</cfquery>
<cfset session.product = getLastID.newID />

How do I update the id of the record, which is lets say 24 rows before this record? I tried it with newID - 24 but that will only work if that record is indeed in the database (if the record was for some reason deleted nothing is updated) For example if the last inserted id is 100, id 76 should be updated but if this id doesn’t exist the one before that should be updated

2

Thanks Oddz. I Indeed could use just the last 24 records added, was it not that the results page, is a combined page, both for normal records as new arrivals. The isNew status is part of the navigation and it is not about the release date but the arrival date. A very old record can be re-released

I like both your suggestions, but would like to go for the first option, since I’m kind of familiar with <cftransaction>. Could you give a very basic example how the transaction with those 3 queries would look like? (specially how do I get the 24th record DESC)

Thank you in advance

3

Hi Stephen thanks for the reply :slight_smile: This is a CMS (for a record store) where just one record at a time is added to the database managed by just one person at a time. The 24 records mentioned are marked as new arrivals isNew=1 (this is the default number of records the owner want to display as new arrivals) They are, as said, flagged as new arrival and ordered by date_added DESC. When a new record is added to the database, the isNew status for the record 24 rows before this new inserted record should be set to 0 isNew=0 no matter the id’s.

So for simplicity. Lets say the below diagram is the product table. Number 10 is the last inserted id and I would like to update the record 3 rows before this last inserted id, which is in this case id 3

ID

1
2
3 (id to be updated)
5
6
10 B[/B]

How would I do so

4

Rows are not ordered and there is no relationship between records based on their generated id (others may have started inserting 50 other records just before you dod and then the record 24 earlier would be in the middle of that group that you didn’t even see were there when you started).

Can you supply more information on just what it is that you want to update another record for and what the relationship is between the records.

5

Thanks rudi, as usual you’re right:) have corrected it already

6

fyi, LIMIT 24,1 gives you the 25th row

7

I wasn’t thinking clearly.


<cfquery name="getLastID" datasource="#application.dsn#">
    SELECT *
    FROM products
    WHERE category_id = 2
    ORDER BY product_id DESC
    LIMIT 24, 1
</cfquery>
8

The most robust solution would be to wrap three queries in a transaction.

The first query would locate the 24th item.
The second query would insert the new item
The third unmark the the result as new from the first

The other option which is just as robust would be to use a trigger. Whenever a new record is inserted check to see if it is a new arrival. If it is a new arrival make the 25th item not new.

Though, why use a flag to determine what items are new? Couldn’t you just display the most recent 24 items based on when they were released or something, rather than using a flag? Are you using a flag because the client wants to be able to control their “new releases”?