• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Mysql java question?

Hello,

Could anyone advise, if would it be possible to write a single sql delete statement, which for each orderno in the order table below. Also deletes the records in the items tables, with the same orderno?

Or would I need to use two delete statements? or even use the inner join?

Thank you

table (order)
orderno:
7823
4633
1788

table (items)
orderno:             item:                   quantity:
7823              Danish pastry                13
7823              Apple pie                      20
4633              Steak pie                      100
0
AntoniRyszard656
Asked:
AntoniRyszard656
  • 9
  • 7
  • 7
  • +1
3 Solutions
 
CEHJCommented:
You can if you have set a cascade delete rule in the table definition, but not otherwise (you'll need two)
0
 
AntoniRyszard656Author Commented:
Hello,

Though, Would I be correct in saying it would be possible to use the select statement to find records in both the order, and items table with the same orderno field. Perhaps using the inner join?
0
 
CEHJCommented:
Yes you would be correct
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AntoniRyszard656Author Commented:
Can the delete statement not be used like the select?  
0
 
CEHJCommented:
No
0
 
CEHJCommented:
If you set the cascade delete rule, both tables will be deleted from automatically when you delete from the parent table
0
 
AntoniRyszard656Author Commented:
Thank you,

Could anyone demonstrate how to use the select statement, and find the records in the order table and item table with the same orderno? Would I possibly need to use the inner join?

table (order)
orderno:
7823
4633
1788

table (items)
orderno:             item:                   quantity:
7823              Danish pastry                13
7823              Apple pie                      20
4633              Steak pie                      100
0
 
CEHJCommented:
select o.orderno, i.item from order o, item i where o.orderno = i.orderno
0
 
objectsCommented:
not so sure you can't do it in a single query, let me ask my db people and get back to you.
0
 
AntoniRyszard656Author Commented:
Thank you,

And to perform this select statement on each of the different orderno's in the order table.

Would I need to create a resultset from the order table, and loop through this, to get the orderno values to plug into the select statement CEHJ gave.
0
 
objectsCommented:
what version of mysql?

(and btw, why aren't you asking this in the mysql ta?)
0
 
objectsCommented:
> And to perform this select statement on each of the different orderno's in the order table.

you don't need a select, just two deletes (or possibly one).
0
 
AntoniRyszard656Author Commented:
I was just asking about setection, rather than deleting records.
0
 
objectsCommented:
what do u need to do a select for? what did u want to select
0
 
AntoniRyszard656Author Commented:
Hello,

I was trying to select the records in the items table which match a specific orderno in the order table.

table (order)
orderno:
7823
4633
1788

table (items)
orderno:             item:                   quantity:
7823              Danish pastry                13
7823              Apple pie                      20
4633              Steak pie                      100
0
 
objectsCommented:
but don't you just want to delete them?
you don't need a select to achieve that

delete from items where orderno=?
0
 
RuadRauFlessaCommented:
delete from items where orderno=? or orderno=? or orderno=? or orderno=?

for multiple deletes in a single query pertaining to diffirent orders.
0
 
CEHJCommented:
>>I was trying to select the records in the items table which match a specific orderno in the order table.

select o.orderno, i.item from order o, item i where o.orderno = i.orderno and o.orderno = 7823
0
 
CEHJCommented:
(
Parameterizable in a PreparedStatement as

select o.orderno, i.item from order o, item i where o.orderno = i.orderno and o.orderno = ?

)
0
 
AntoniRyszard656Author Commented:
Thank you,

Could I ask finally, if I had two tables, named author and book and tried to test if there isbn field was equal to a variable isbn.

And wrote a select statement as here: would using the AND tool mean the isbn would (have) to be found in both tables?

sqlquery = "SELECT * FROM author a, book b WHERE a.isbn =" + isbn + " AND b.title =" + isbn;

Thank you
0
 
AntoniRyszard656Author Commented:

And rather than using the AND tool is there an alternative OR. So the isbn could be found in both tables or just one?

Thank you
0
 
objectsCommented:
> And wrote a select statement as here: would using the AND tool mean the isbn would (have) to be found in both tables?

yes

> And rather than using the AND tool is there an alternative OR. So the isbn could be found in both tables or just one?

yes
0
 
AntoniRyszard656Author Commented:
Hello,

If I changed to sql to include OR:

sqlquery = "SELECT * FROM author a, book b WHERE a.isbn =" + isbn + " OR b.title =" + isbn;

Normally in programming (or) means to take one action, or an alternative. Does this mean if I use (or) in this sql statement, the records will be selected from the author or book, but could not gather be from both.

I just wanted to sure, I understood.
0
 
objectsCommented:
no it means to only include records in the result set where either of the conditions are met.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now