h******3 发帖数: 351 | 1 可能讨论过:
Table "books" has columns BOOK_ID, BOOK_TITLE, BOOK_AUTHOR, PRICE, PUBLISH_
DATE. Delete most expensive book from books table.
我try 了
delete max(price), author, publish_date,book_id from books;
sub-query
delete * from books where price in (select max(price) from books);
self-join
delete * from books b1, books b2 where b1.price = b2.price and b2.price in
select max(price);
none of them work. Can anybody tell me one executable SQL statement?
Thanks. | g**********1 发帖数: 1113 | | t*****j 发帖数: 1105 | 3 delete 肯定是 delete一个row,全部的column啦。
【在 h******3 的大作中提到】 : 可能讨论过: : Table "books" has columns BOOK_ID, BOOK_TITLE, BOOK_AUTHOR, PRICE, PUBLISH_ : DATE. Delete most expensive book from books table. : 我try 了 : delete max(price), author, publish_date,book_id from books; : sub-query : delete * from books where price in (select max(price) from books); : self-join : delete * from books b1, books b2 where b1.price = b2.price and b2.price in : select max(price);
| p*****s 发帖数: 4393 | 4 try to get rid of your * after delete in your second statement
【在 h******3 的大作中提到】 : 可能讨论过: : Table "books" has columns BOOK_ID, BOOK_TITLE, BOOK_AUTHOR, PRICE, PUBLISH_ : DATE. Delete most expensive book from books table. : 我try 了 : delete max(price), author, publish_date,book_id from books; : sub-query : delete * from books where price in (select max(price) from books); : self-join : delete * from books b1, books b2 where b1.price = b2.price and b2.price in : select max(price);
| d**e 发帖数: 6098 | 5 delete from books
where price = (select max(price) from books);
commit;
【在 h******3 的大作中提到】 : 可能讨论过: : Table "books" has columns BOOK_ID, BOOK_TITLE, BOOK_AUTHOR, PRICE, PUBLISH_ : DATE. Delete most expensive book from books table. : 我try 了 : delete max(price), author, publish_date,book_id from books; : sub-query : delete * from books where price in (select max(price) from books); : self-join : delete * from books b1, books b2 where b1.price = b2.price and b2.price in : select max(price);
| x***y 发帖数: 633 | 6 In mySQL,
delete from books
order by price desc
limit 1 \g
【在 h******3 的大作中提到】 : 可能讨论过: : Table "books" has columns BOOK_ID, BOOK_TITLE, BOOK_AUTHOR, PRICE, PUBLISH_ : DATE. Delete most expensive book from books table. : 我try 了 : delete max(price), author, publish_date,book_id from books; : sub-query : delete * from books where price in (select max(price) from books); : self-join : delete * from books b1, books b2 where b1.price = b2.price and b2.price in : select max(price);
| h******3 发帖数: 351 | 7 tried
delete from books where price = (select max(price) from books);
it doesn't work. Error code: 1093, you can't specify target table "books"
for update in from clause. | h******3 发帖数: 351 | 8 it works. :P.
【在 x***y 的大作中提到】 : In mySQL, : delete from books : order by price desc : limit 1 \g
|
|