Why update and select both is not working with same table

Question

In this query i want to update those records which is latest posted.But my this query is not working please help me what the reason ???

Error :--You can't specify target table 'beevers_products' for update in FROM clause

update beevers_products set product_name='my_product_name' where posted_date in (SELECT posted_date FROM `beevers_products` order by posted_date asc limit 1)

Answer 1

Check this:

UPDATE beevers_products 
SET product_name = 'my_product_name' 
WHERE posted_date = (SELECT posted_date 
                     FROM beevers_products
                     ORDER BY posted_date DESC limit 1)

Answer 2

Recommend you to use CI Active Record class for querying.

http://ellislab.com/codeigniter/user-guide/database/active_record.html

Answer 3

Try this:

update beevers_products as t1, 
(select posted_date from beevers_products order by posted_date asc limit 1) as t2
set t1.product_name = 'my_product_name'
where t1.posted_date in (t2.posted_date);

You will have to give alias to the record you want and use it in where clause.

Answer 4

Try this

UPDATE beevers_products 
SET product_name = 'my_product_name'  
OUTPUT DELETED.product_name
WHERE posted_date in (SELECT posted_date 
                      FROM `beevers_products` 
                      order by posted_date asc 
                      limit 1)

Read more on output

Check this -->Can I update/select from a table in one query?
May be it helps you

Answer 5

INSERT INTO beevers_products (id, product_name)
SELECT id, 'my_product_name'
FROM beevers_products
ORDER BY posted_date ASC
LIMIT 1
ON DUPLICATE KEY UPDATE product_name = VALUES(product_name)

Once I learned to use INSERT ... SELECT ... ON DUPLICATE so many possibilities surfaced. I'm just a bit curious whenever you want posted_data ASC or posted_data DESC.