topic Re: Native SQL Update statement error when more than one field used in Application Development and Automation Discussions

Native SQL Update statement error when more than one field used

Rodrigo-Giner — Tue, 28 Aug 2007 19:42:38 GMT

Hi, I would make it simple. I update directly to a SQL server.

When I update only one field works.


LOOP AT IT_MAT.
 EXEC SQL.
 UPDATE Articulos SET PrecioSinImp = :IT_MAT-PRECIOSINIMP        WHERE Modelo = :IT_MAT-MODELO 
 ENDEXEC.
ENDLOOP.

This give me sy-subrc = 0

When I Try to update several fields doesnt works, any would can help me ?.


LOOP AT IT_MAT.
 EXEC SQL.
  UPDATE Articulos SET PrecioSinImp = :IT_MAT-PRECIOSINIMP
                       Descripcion = :IT_MAT-DESCRIPCION
  WHERE Modelo = :IT_MAT-MODELO 
 ENDEXEC.
ENDLOOP.

This give me dump SQL error 170.

2- BTW, I have to leave this report program in a job so the data would be updated once a day in a job, so I have to update the existing data and add new one.

I Was thinking doing something like this.

LOOP AT it_table.

UPDATE DQL TABLE

IF sy-subrc <> 0.

INSERT DQL TABLE

ENDIF.

ENDLOOP.

This may works ??? because if this not work, I have to select single each one of the data in the SQL table, if it was founded UPDATE otherwise INSERT. And I dont like this aproach.

Thx

Re: Native SQL Update statement error when more than one field used

Former Member — Tue, 28 Aug 2007 20:09:53 GMT

What database are you using? MSSQL?

Rob

Re: Native SQL Update statement error when more than one field used

Former Member — Tue, 28 Aug 2007 22:12:30 GMT

Hi Rodrigo,

I feel that your requirement is as below:

Depending on the criteria in the where clause, check whether the record exists or not.. If it does, then update the record with new values, else, create a new record..

You can do the same as below:

LOOP AT it_mat INTO e_mat. (e_mat of same structure as it_mat)

MOVE : xxx TO e_mat-yyy... and so on (Only change the non-key values)

MODIFY dbtable FROM e_mat.

ENDLOOP.

This code will create a new record if a record with the given key values in e_mat is not found, else, it will update the existing record with the new non-key values in the structure e_mat.

Thanks and Best Regards,

Vikas Bittera.

Message was edited by:

Alvaro Tejada Galindo

Re: Native SQL Update statement error when more than one field used

Rodrigo-Giner — Wed, 29 Aug 2007 12:39:32 GMT

Hi, Vikas. The thing is that I have to do this using Native SQL, not with OPEN SQL.

And yes Rob, Im using a SQL server.

What I need is the Exact syntax for the UPDATE statement using Native SQL with a SQL server.

Thx

Re: Native SQL Update statement error when more than one field used

Former Member — Wed, 29 Aug 2007 13:37:13 GMT

Native SQL is dependent on your database, not ABAP, so I would find an MS SQL forum and ask your question there (or better yet, find an MS SQL manual).

Rob

Re: Native SQL Update statement error when more than one field used

Rodrigo-Giner — Wed, 29 Aug 2007 14:24:58 GMT

hi Rob, I have found the syntax.

<b>Update several Columns in a Row

We want to change the address and add the name of the city:

UPDATE Person

SET Address = 'Stien 12', City = 'Stavanger'

WHERE LastName = 'Rasmussen'</b>

each field is separated by "," but doesnt work either, now it throw me a dump with "SQL error 156".

any ideas ?

Re: Native SQL Update statement error when more than one field used

Former Member — Wed, 29 Aug 2007 15:05:06 GMT

Same as before - it should be in the manual.

Rob

Re: Native SQL Update statement error when more than one field used

Rodrigo-Giner — Wed, 29 Aug 2007 18:09:05 GMT

I was doing something wrong

UPDATE Person

SET Address = 'Stien 12', City = 'Stavanger',

WHERE LastName = 'Rasmussen'

Instead of -_-

UPDATE Person

SET Address = 'Stien 12', City = 'Stavanger'

WHERE LastName = 'Rasmussen'