Re: [SQL] How to delete multiple records
Hi, Javanesevn, Javanesevn wrote: > I execute this query on below: > > delete from PRODUCT > where exists ( > select > product_id, item_id > from PRODUCT > where >research_date < '2006-01-01' > ) > this query deleted all records data in PRODUCT table. The subquery > return only some records. Yes, that's correct. You don't have any interconnection between the inner and the outer query. So, for every outer row, there exists an inner row which has research_date < '2006-01-01'. > Tell me about other idea? and What's wrong from this query. For this simple case, why don't you do: DELETE FROM PRODUCT WHERE research_date < '2006-01-01' Or do you want to delete all entries for products that have at least one entry with a matching research date? Then go along something like DELETE FROM product WHERE EXISTS ( SELECT * from product innr WHERE research_date < '2006-01-01' AND product_id = innr.product_id AND item_id = innr.item_id ); HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to delete multiple records
On Thu, Sep 14, 2006 at 03:07:35PM +0900, Javanesevn wrote: > Dear all, > > I execute this query on below: > > delete from PRODUCT > where exists ( > select > product_id, item_id > from PRODUCT > where >research_date < '2006-01-01' > ) > this query deleted all records data in PRODUCT table. The subquery > return only some records. > Tell me about other idea? and What's wrong from this query. The easiest way is to do it like this: delete from PRODUCT where research_date < '2006-01-01' But if you really want to use a subquery, you want to relate it to the current record: delete from PRODUCT where product_in in ( select product_id from PRODUCT where research_date < '2006-01-01' ) In your example you just test if some records exist. They allways do, so the where clause evaluates to true for every row -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] nested select within a DISCTINCT block
Hi, here i have a problem with this task... I have a table "cancellation" which stores cancelled bookings and details of charges etc and a table "bookings" which stores details of bookings, for example: cancellation(cancellation_id, booking_id, charge) booking(booking_id, customer_id, product_package_id, details) in the query, i wish to find, how many customers have booked for each product_package_id. if there were 3 bookings for product_package_id=1, and all these are cancelled and therefore exist in cancellation, then the query result shoud display something like package_id, #of bookings 1 0 here are what i tried select distinct b.product_package_id, count (distinct b.customer_id and not exists (select cc from cancellation cc where cc.booking_id=b.booking_id)) from booking as b group by b.product_package_id and it doesnt work. the syntax within the DISTINCT is wrong, unsurprisingly. i also tried select distinct b.product_package_id, count (distinct b.customer_id not in (select cc from cancellation cc where cc.booking_id=b.booking_id)) from booking as b group by b.product_package_id it produced incorrect result. ie, for those canceled bookings are also counted, producing package_id, #of bookings 1 3 which supposed to be package_id, #of bookings 1 0 could anyone give any hints please, many thanks ! -- View this message in context: http://www.nabble.com/nested-select-within-a-DISCTINCT-block-tf2272951.html#a6310575 Sent from the PostgreSQL - sql forum at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] nested select within a DISCTINCT block
On 9/14/06 1:13 PM, "zqzuk" <[EMAIL PROTECTED]> wrote: > > Hi, here i have a problem with this task... > > I have a table "cancellation" which stores cancelled bookings and details of > charges etc > and a table "bookings" which stores details of bookings, for example: > > cancellation(cancellation_id, booking_id, charge) > booking(booking_id, customer_id, product_package_id, details) > > in the query, i wish to find, how many customers have booked for each > product_package_id. if there were 3 bookings for product_package_id=1, and > all these are cancelled and therefore exist in cancellation, then the query > result shoud display something like > > package_id, #of bookings > 1 0 > > > here are what i tried > > select distinct b.product_package_id, > count (distinct b.customer_id and not exists (select cc from cancellation cc > where cc.booking_id=b.booking_id)) from booking as b > group by b.product_package_id > > and it doesnt work. the syntax within the DISTINCT is wrong, unsurprisingly. > > > i also tried > select distinct b.product_package_id, > count (distinct b.customer_id not in (select cc from cancellation cc where > cc.booking_id=b.booking_id)) from booking as b > group by b.product_package_id > > it produced incorrect result. ie, for those canceled bookings are also > counted, producing > package_id, #of bookings > 1 3 > > which supposed to be > package_id, #of bookings > 1 0 > > > could anyone give any hints please, many thanks ! > create table booking(booking_id int, customer_id int, product_package_id int, details text); create table cancellation(cancellation_id int , booking_id int, charge decimal); insert into booking values( 1, 1, 1, 'Cxl Booking 1' ); insert into booking values( 2, 2, 1, 'Cxl Booking 2' ); insert into booking values( 3, 2, 1, 'Ok Booking 3' ); insert into booking values( 4, 3, 2, 'Cxl Booking 4' ); insert into cancellation values( 1, 1, 1.00 ); insert into cancellation values( 2, 2, 1.00 ); insert into cancellation values( 3, 4, 1.00 ); select distinct product_package_id, ( select count(booking_id) from booking b2 where b2.product_package_id = b1.product_package_id and not exists ( select 1 from cancellation c where c.booking_id = b2.booking_id ) ) as uncancelled_bookings from booking b1 order by product_package_id; product_package_id uncancelled_bookings - --- 1 1 2 0 2 record(s) selected [Fetch MetaData: 2/ms] [Fetch Data: 0/ms] [Executed: 9/14/06 5:56:07 PM EDT ] [Execution: 86/ms] -- Daryl http://itsallsemantics.com "I¹m afraid of the easy stuff its always harder than it seems" -- Bill Hampton, 2006 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] nested select within a DISCTINCT block
Thanks alot!!! Daryl Richter-2 wrote: > > On 9/14/06 1:13 PM, "zqzuk" <[EMAIL PROTECTED]> wrote: > >> >> Hi, here i have a problem with this task... >> >> I have a table "cancellation" which stores cancelled bookings and details >> of >> charges etc >> and a table "bookings" which stores details of bookings, for example: >> >> cancellation(cancellation_id, booking_id, charge) >> booking(booking_id, customer_id, product_package_id, details) >> >> in the query, i wish to find, how many customers have booked for each >> product_package_id. if there were 3 bookings for product_package_id=1, >> and >> all these are cancelled and therefore exist in cancellation, then the >> query >> result shoud display something like >> >> package_id, #of bookings >> 1 0 >> >> >> here are what i tried >> >> select distinct b.product_package_id, >> count (distinct b.customer_id and not exists (select cc from cancellation >> cc >> where cc.booking_id=b.booking_id)) from booking as b >> group by b.product_package_id >> >> and it doesnt work. the syntax within the DISTINCT is wrong, >> unsurprisingly. >> >> >> i also tried >> select distinct b.product_package_id, >> count (distinct b.customer_id not in (select cc from cancellation cc >> where >> cc.booking_id=b.booking_id)) from booking as b >> group by b.product_package_id >> >> it produced incorrect result. ie, for those canceled bookings are also >> counted, producing >> package_id, #of bookings >> 1 3 >> >> which supposed to be >> package_id, #of bookings >> 1 0 >> >> >> could anyone give any hints please, many thanks ! >> > > create table booking(booking_id int, customer_id int, product_package_id > int, details text); > > create table cancellation(cancellation_id int , booking_id int, charge > decimal); > > insert into booking values( 1, 1, 1, 'Cxl Booking 1' ); > insert into booking values( 2, 2, 1, 'Cxl Booking 2' ); > insert into booking values( 3, 2, 1, 'Ok Booking 3' ); > insert into booking values( 4, 3, 2, 'Cxl Booking 4' ); > > insert into cancellation values( 1, 1, 1.00 ); > insert into cancellation values( 2, 2, 1.00 ); > insert into cancellation values( 3, 4, 1.00 ); > > > select distinct product_package_id, >( select count(booking_id) > from booking b2 > where > b2.product_package_id = b1.product_package_id > and not exists ( select 1 from cancellation c where c.booking_id = > b2.booking_id ) ) as uncancelled_bookings > from booking b1 > order by product_package_id; > > product_package_id uncancelled_bookings > - --- > 1 1 > 2 0 > > 2 record(s) selected [Fetch MetaData: 2/ms] [Fetch Data: 0/ms] > > [Executed: 9/14/06 5:56:07 PM EDT ] [Execution: 86/ms] > > > -- > Daryl > http://itsallsemantics.com > > "I¹m afraid of the easy stuff its always harder than it seems" > -- Bill Hampton, 2006 > > > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > > -- View this message in context: http://www.nabble.com/nested-select-within-a-DISCTINCT-block-tf2272951.html#a6315840 Sent from the PostgreSQL - sql forum at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] nested select within a DISCTINCT block
On 14 sep 2006, at 23.58, Daryl Richter wrote: create table booking(booking_id int, customer_id int, product_package_id int, details text); create table cancellation(cancellation_id int , booking_id int, charge decimal); insert into booking values( 1, 1, 1, 'Cxl Booking 1' ); insert into booking values( 2, 2, 1, 'Cxl Booking 2' ); insert into booking values( 3, 2, 1, 'Ok Booking 3' ); insert into booking values( 4, 3, 2, 'Cxl Booking 4' ); insert into cancellation values( 1, 1, 1.00 ); insert into cancellation values( 2, 2, 1.00 ); insert into cancellation values( 3, 4, 1.00 ); select distinct product_package_id, ( select count(booking_id) from booking b2 where b2.product_package_id = b1.product_package_id and not exists ( select 1 from cancellation c where c.booking_id = b2.booking_id ) ) as uncancelled_bookings from booking b1 order by product_package_id; product_package_id uncancelled_bookings - --- 1 1 2 0 Given the above, you could also phrase it a little more natural, as follows: SELECT product_package_id, COUNT(b.booking_id)-COUNT(c.booking_id) AS un_cancelled_bookings FROM booking b LEFT JOIN cancellation c USING(booking_id) GROUP BY product_package_id ORDER BY product_package_id; I don't know about the amount and distribution of data in this case, but I think this will also give you a slightly better plan in most cases. Sincerely, Niklas Johansson ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
