在这个查询中,我想更新那些最新发布的记录。但是我的这个问题不起作用,请帮我什么原因???
错误:--不能在FROM子句中为更新指定目标表"beevers_products"
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)
检查此项:
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)
建议您使用CI Active Record类进行查询。
http://ellislab.com/codeigniter/user-guide/database/active_record.html
试试这个
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)
阅读更多关于输出的信息
选中此-->是否可以在一个查询中更新/选择表
也许它可以帮助你
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)
一旦我学会了使用INSERT ... SELECT ... ON DUPLICATE
,许多可能性就出现了。每当你想要posted_data ASC
或posted_data DESC
时,我只是有点好奇。
试试这个:
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子句中使用它。