topic Re: Update tabel SQL statement in Application Development and Automation Discussions

Update tabel SQL statement

Former Member — Tue, 09 Aug 2005 06:31:56 GMT

I try to update a database tabel, but I got an error SQL 170:


  LOOP AT g_import_tab.

    EXEC SQL.

      UPDATE MYTABLE
      flag =: g_import_tab-flag,
      lgnum =: g_import_tab-lgnum,
      tanum =: g_import_tab-tanum,
      tapos =: g_import_tab-tapos,
      squit =: g_import_tab-squit,
      lenum =: g_import_tab-lenum,
      quknz =: g_import_tab-quknz
      where lgnum = g_import_tab-lgnum
      and   tanum = g_import_tab-tanum

    ENDEXEC.

  ENDLOOP.

Can you see in the code, what I do wrong?

Re: Update tabel SQL statement

jayanthi_jayaraman — Tue, 09 Aug 2005 06:35:41 GMT

Hi,

Try this.

LOOP AT g_import_tab.

EXEC SQL.

UPDATE MYTABLE SET

flag =: g_import_tab-flag,

lgnum =: g_import_tab-lgnum,

tanum =: g_import_tab-tanum,

tapos =: g_import_tab-tapos,

squit =: g_import_tab-squit,

lenum =: g_import_tab-lenum,

quknz =: g_import_tab-quknz

where lgnum = g_import_tab-lgnum

and tanum = g_import_tab-tanum;

ENDEXEC.

ENDLOOP.

But I don't think to update database inside loop.

Kindly reward points if you find it as useful by clciking the star on the left of reply.

Message was edited by: Jayanthi Jayaraman

Re: Update tabel SQL statement

Former Member — Tue, 09 Aug 2005 06:51:54 GMT

It didn't solved the problem

I have now changed my code, so it isn't inside a loop, and made some hardcode.

My code is now like this:


  EXEC SQL.
    UPDATE e1ltco SET
   flag =: '1',
      where lgnum = '100'
      and   tanum = '0001322808'
      and   tapos = '0001'
      and   squit = 'X'
      and   lenum = '00357096784019929423'
      and   quknz = 'X'
  ENDEXEC.

And I still get the SQL error 170

Re: Update tabel SQL statement

Former Member — Tue, 09 Aug 2005 06:56:45 GMT

Hi, don't update the DB in a loop.

As my recommandation, if possbile, try to achieve it in a

Update from TABLE XXXX

That will be fine on the performance.

Thanks

Re: Update tabel SQL statement

jayanthi_jayaraman — Tue, 09 Aug 2005 06:58:31 GMT

Hi,

Semicolon is necessary.Remove the comma.

EXEC SQL.

UPDATE e1ltco SET flag = '1'

where lgnum = '100'

and tanum = '0001322808'

and tapos = '0001'

and squit = 'X'

and lenum = '00357096784019929423'

and quknz = 'X' ;

ENDEXEC.

If this is not working,

check this.

EXEC SQL.

UPDATE table e1ltco SET flag = '1' where lgnum = '100'

and tanum = '0001322808'

and tapos = '0001'

and squit = 'X'

and lenum = '00357096784019929423'

and quknz = 'X' ; ENDEXEC.