 |
AppletTalk.com
Java discussions newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
William Krick
Guest
|
 Posted: Fri Mar 04, 2005 11:03 pm Post subject: SQL: how to update the results from a select query |
 |
|
I'm working on a java application and I've gotten stuck on an SQL
problem.
Consider the following query...
SELECT status
FROM customers
LEFT JOIN orders
ON customers.ID = orders.customerID
WHERE orders.partnum = 123
This returns a set of rows.
I would like to update the status field on all rows that are returned
by this query.
I'm thinking I need to do something like this but I'm not sure of the
syntax...
UPDATE customers
SET status = true
WHERE EXISTS (
SELECT status
FROM customers
LEFT JOIN orders
ON customers.ID = orders.customerID
WHERE orders.partnum = 123
)
Can anybody point me in the right direction?
|
|
| Back to top |
|
 |
Lee Fesperman
Guest
|
| Posted: Fri Mar 04, 2005 11:38 pm Post subject: Re: SQL: how to update the results from a select query |
|
|
William Krick wrote:
| Quote: |
I'm working on a java application and I've gotten stuck on an SQL
problem.
Consider the following query...
SELECT status
FROM customers
LEFT JOIN orders
ON customers.ID = orders.customerID
WHERE orders.partnum = 123
This returns a set of rows.
I would like to update the status field on all rows that are returned
by this query.
I'm thinking I need to do something like this but I'm not sure of the
syntax...
UPDATE customers
SET status = true
WHERE EXISTS (
SELECT status
FROM customers
LEFT JOIN orders
ON customers.ID = orders.customerID
WHERE orders.partnum = 123
)
Can anybody point me in the right direction?
|
Your syntax is fine. However, some DBMSs don't support self-referencing subqueries in
UPDATE. This is more portable and likely more efficient:
UPDATE customers
SET status = true
WHERE ID IN (SELECT customerID FROM orders WHERE partnum = 123)
For additional efficiency, you might add:
AND status <> true
.... to the outer WHERE clause.
--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
|
|
| Back to top |
|
|
Chuck Simpson
Guest
|
| Posted: Sat Mar 05, 2005 2:53 pm Post subject: Re: SQL: how to update the results from a select query |
|
|
On Fri, 04 Mar 2005 15:03:06 -0800, William Krick wrote:
| Quote: | I'm working on a java application and I've gotten stuck on an SQL problem.
Consider the following query...
SELECT status
FROM customers
LEFT JOIN orders
ON customers.ID = orders.customerID
WHERE orders.partnum = 123
This returns a set of rows.
I would like to update the status field on all rows that are returned by
this query.
I'm thinking I need to do something like this but I'm not sure of the
syntax...
UPDATE customers
SET status = true
WHERE EXISTS (
SELECT status
FROM customers
LEFT JOIN orders
ON customers.ID = orders.customerID
WHERE orders.partnum = 123
)
Can anybody point me in the right direction?
|
First, a LEFT JOIN does not make any sense if you are going to place the
"orders.partnum = 123" condition on the right hand table. You would use a
LEFT JOIN to find out which customers had no orders. In this case an INNER
or NATURAL JOIN is sufficient.
How you do the update depends on the capability of your database. I assume
you are not using Oracle. If you are using Postgres your update statement
should work. If you are using MySQL then it depends on which version. I
believe the latest version supports subselects but I don't use MySQL so
don't take that as gospel.
The following update statement is equivalent to the one you show but is a
little simpler and may perform better as the join in the subselect is
unnecessary.
UPDATE customers
SET status = true
WHERE ID in (
SELECT customerID
FROM orders
WHERE partnum = 123)
Hope this helps.
Chuck
|
|
| Back to top |
|
|
William Krick
Guest
|
| Posted: Mon Mar 07, 2005 5:12 pm Post subject: Re: SQL: how to update the results from a select query |
|
|
Chuck Simpson wrote:
| Quote: | On Fri, 04 Mar 2005 15:03:06 -0800, William Krick wrote:
The following update statement is equivalent to the one you show but
is a
little simpler and may perform better as the join in the subselect is
unnecessary.
UPDATE customers
SET status = true
WHERE ID in (
SELECT customerID
FROM orders
WHERE partnum = 123)
|
I think this would definitely work.
Unfortunately, the version of MySQL that my web host provides (4.0.22
standard) doesn't support subqueries.
Is there any way I can get the same behavior without subselects?
|
|
| Back to top |
|
|
Chuck Simpson
Guest
|
| Posted: Tue Mar 08, 2005 4:13 pm Post subject: Re: SQL: how to update the results from a select query |
|
|
On Mon, 07 Mar 2005 09:12:13 -0800, William Krick wrote:
| Quote: |
Chuck Simpson wrote:
On Fri, 04 Mar 2005 15:03:06 -0800, William Krick wrote:
The following update statement is equivalent to the one you show but
is a
little simpler and may perform better as the join in the subselect is
unnecessary.
UPDATE customers
SET status = true
WHERE ID in (
SELECT customerID
FROM orders
WHERE partnum = 123)
I think this would definitely work.
Unfortunately, the version of MySQL that my web host provides (4.0.22
standard) doesn't support subqueries.
Is there any way I can get the same behavior without subselects?
|
Probably not. Instead perform the select query and save the customerId
values in a list. Then build the update statement string from the list of
customerId's and execute the statement string. In Java it would look
something like the following: (NOTE try/catch/finally ommitted for clarity)
Connection con = ...;
String query = "SELECT customerID FROM orders WHERE partnum = 123";
StringBuffer update = "UPDATE customers SET status = true WHERE ID in (";
List list = new ArrayList();
Statement s = con.createStatement();
ResultSet rs = s.executeQuery(query); while(rs.next()) {
list.add(rs.getString(1));
}
rs.close();
for(int i = 0, n = list.size() - 1; i < n; i++) {
// this assumes ID is a number, if it is a string
// add update.append("'"); before and after the next statement
update.append(list.get(i));
update.append(",");
}
update.append(list.get(list.size()-1);
update.append(")");
int count = s.executeUpdate(update.toString());
if(count != list.size())
throw SomeException("update count not equal ID count");
If the number of customerID's is too great to process all at once you may
have to build and execute multiple update statements. In that case create
2 Statements (one for the query and one for the update) so that execution
of the the update statement does not close your ResultSet.
Chuck
|
|
| Back to top |
|
|
William Krick
Guest
|
| Posted: Mon Mar 21, 2005 8:38 pm Post subject: Re: SQL: how to update the results from a select query |
|
|
Chuck Simpson wrote:
| Quote: | On Mon, 07 Mar 2005 09:12:13 -0800, William Krick wrote:
Chuck Simpson wrote:
On Fri, 04 Mar 2005 15:03:06 -0800, William Krick wrote:
The following update statement is equivalent to the one you show
but
is a
little simpler and may perform better as the join in the subselect
is
unnecessary.
UPDATE customers
SET status = true
WHERE ID in (
SELECT customerID
FROM orders
WHERE partnum = 123)
I think this would definitely work.
Unfortunately, the version of MySQL that my web host provides
(4.0.22
standard) doesn't support subqueries.
Is there any way I can get the same behavior without subselects?
Probably not. Instead perform the select query and save the
customerId
values in a list. Then build the update statement string from the
list of
customerId's and execute the statement string. In Java it would look
something like the following: (NOTE try/catch/finally ommitted for
clarity)
Connection con = ...;
String query = "SELECT customerID FROM orders WHERE partnum = 123";
StringBuffer update = "UPDATE customers SET status = true WHERE ID in
(";
List list = new ArrayList();
Statement s = con.createStatement();
ResultSet rs = s.executeQuery(query); while(rs.next()) {
list.add(rs.getString(1));
}
rs.close();
for(int i = 0, n = list.size() - 1; i < n; i++) {
// this assumes ID is a number, if it is a string
// add update.append("'"); before and after the next statement
update.append(list.get(i));
update.append(",");
}
update.append(list.get(list.size()-1);
update.append(")");
int count = s.executeUpdate(update.toString());
if(count != list.size())
throw SomeException("update count not equal ID count");
If the number of customerID's is too great to process all at once you
may
have to build and execute multiple update statements. In that case
create
2 Statements (one for the query and one for the update) so that
execution
of the the update statement does not close your ResultSet.
Chuck
|
Just an update, I managed to get it working with just one statement.
This is the query we ended up using...
UPDATE CUSTOMERS
LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMERID
SET CUSTOMER.STATUS = TRUE
WHERE ORDERS.PARTNUM = 123
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
