How to write a update SQL using a select SQL

Question

I want to write a update SQL statement, but one conidtion of this statement is the result from a select SQL statement, and I also want to return the result of the select SQL statement.

Like this: update ... set ... where id = (select id from ...)

I want to return the value of id back.

Does anybody know how should I do this?

Thanks in advance!

Probably you should create a procedure in your RDBMS and call that from java. — Usman Saleem
– Usman Saleem, Commented Oct 7, 2011 at 7:39

andypandy · Accepted Answer · 2011-10-07 07:36:42Z

2

I don't believe that's possible in one statement. Update then query (select) the new value, or query the value first, and then submit an update.

Alternative would be a stored procedure on the database, which executes the multiple queries and returns the result for you.

answered Oct 7, 2011 at 7:36

andypandy

1,1177 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Usman Saleem Over a year ago

Yes, I would follow stored procedure approach.

andypandy Over a year ago

A word of warning with store procedures: Unless you're using them already, they will take you down a route which becomes database specific. If you want to remain DB agnostic, I'd tend to stay with multiple simple sql queries.

michael667 · Accepted Answer · 2011-10-07 07:36:44Z

0

This is not possible in all Java database frameworks that I know. Probably you need to separate your query and update in Java.

answered Oct 7, 2011 at 7:36

michael667

3,26026 silver badges32 bronze badges

Comments

Janick Bernet · Accepted Answer · 2011-10-07 07:52:44Z

0

I don't see any problem in using a subselect in a WHERE clause of an update statement.

For the second request, getting back the value of id, I know this is possible in DB2, and maybe others implement that syntax too:

SELECT id FROM FINAL TABLE (
  update ... set ... where id = (select id from ...)
)

This works also for INSERT and DELETE statements. (See the documentation.)

answered Oct 7, 2011 at 7:52

Janick Bernet

21.2k2 gold badges33 silver badges56 bronze badges

Comments

Thomas · Accepted Answer · 2011-10-07 07:53:12Z

0

Update statements won't return the updated datasets. The select in that case would be a subselect that isn't directly accessible.

You'd thus have to use at least two queries:

select the ids you want
call the update query passing the previously selected ids as a parameter

answered Oct 7, 2011 at 7:53

Thomas

88.9k13 gold badges126 silver badges162 bronze badges

Collectives™ on Stack Overflow

How to write a update SQL using a select SQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related