SQL UPDATE/SELECT Statement in Java

[Former Member]

Hi,

i want to Select some Data from my Database.

An in this data i will update a field.

So that the next select will take only data without the value in the field.

Its look like that but didn't work:

PreparedStatement pstmt2 =
con.prepareStatement("UPDATE SAP_POS_ARH_RN_ZAGLAV set PRIJENOS_SIFRA=? in(SELECT TOP 10 * FROM SAP_POS_ARH_RN_ZAGLAV where PRIJENOS_SIFRA = 0");
pstmt2.setString(1, reqID);
ResultSet rs2 = pstmt2.executeQuery();

any Idea? i get the error

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Incorrect syntax near the keyword 'in'.

Regards,

Robin

[Former Member]

Hi,

you can't use a select <b>*</b> in the sub-select of your update clause. That doesn't work out. The sub select can only refer to one single field as Raja has suggested.

An Update Query will never return the updated rows. But only do the update on the selected rows.

If you want to retrieve the updated rows afterwards, you will have to perform a second JDBC call.

And as far as I know the update statement will be excecuted with the following code:

pstmt2.execute();

(Remember, it won't return a result set..)

Regards, Astrid

[Former Member]

Hi,

Try like this

"UPDATE SAP_POS_ARH_RN_ZAGLAV set PRIJENOS_SIFRA=? from

SAP_POS_ARH_RN_ZAGLAV where PRIJENOS_SIFRA in(SELECT TOP 10 PRIJENOS_SIFRA FROM SAP_POS_ARH_RN_ZAGLAV where PRIJENOS_SIFRA = 0"

[Former Member]

UPDATE SAP_POS_ARH_RN_ZAGLAV set PRIJENOS_SIFRA=? from SAP_POS_ARH_RN_ZAGLAV where PRIJENOS_SIFRA in(SELECT TOP 1 * FROM SAP_POS_ARH_RN_ZAGLAV where PRIJENOS_SIFRA = 0)

• because i need every value from this row.

But I get Error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

What does this mean?

[Former Member]

Hi,

this means, that you can only compare the column PRIJENOS_SITRA with one value, but your subquery returns multiple values (columns).

The correct statement should simply be:

UPDATE SAP_POS_ARH_RN_ZAGLAV

set PRIJENOS_SIFRA=?

from SAP_POS_ARH_RN_ZAGLAV

where PRIJENOS_SIFRA = 0

With this statement you update the selected data at once. You don't read any data from the database. The subquery in your initial statement is only used by the database to identify the data to be updated.

If you need the data for further processing you have to invoke a single select statement and use the ResultSet object to read the data.

Hope this helps.

Rgds.

Jürgen

[Former Member]

But in want to update only in the first 10 rows the value.

when i use

UPDATE SAP_POS_ARH_RN_ZAGLAV

set PRIJENOS_SIFRA=?

from SAP_POS_ARH_RN_ZAGLAV

where PRIJENOS_SIFRA = 0

he update every 0 to new Value.

Regards,

Robin

[Former Member]

Ok, then you have to do it this way:

UPDATE SAP_POS_ARH_RN_ZAGLAV

set PRIJENOS_SIFRA=?

from SAP_POS_ARH_RN_ZAGLAV

where ( are all your identifying colums.

The inner select statement selects 10 rows with the given condition (PRIJENOS_SIFRA = 0). The Update statement updates the resulting rows.

Rgds.

Jürgen