Geek asks and answers

Why updating and selecting both do not work with the same table

In this query, I want to update the posts that were last published. But my request is not working, please help me, what is the reason ???

Error: - You cannot specify the target table "beevers_products" for updating in the 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)
+5
source share
5 answers

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)
+1
source

We recommend using the CI Active Record class for queries.

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

0
source

:

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);

where.

0

try it

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)

More on output

Check this -> Can I update / select from a table in a single query?
Maybe this will help you

0
source 
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)

As soon as I learned to use INSERT ... SELECT ... ON DUPLICATE, many opportunities appeared. I'm just curious when you want posted_data ASCor posted_data DESC.

0
source

More articles:


All Articles