Native SQL Update statement error when more than one field used

[Rodrigo-Giner]

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

1-

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

[Former Member]

What database are you using? MSSQL?

Rob

[Former Member]

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.

<b><REMOVED BY MODERATOR></b>

Message was edited by:

Alvaro Tejada Galindo

[Rodrigo-Giner]

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

[Former Member]

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

[Rodrigo-Giner]

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 ?

[Former Member]

Same as before - it should be in the manual.

Rob

[Rodrigo-Giner]

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'