Update commad, DB2 novice question...

[ChrisRutherford]

Hello,

I come form the world of SQL 7 / 2000. I'm trying to run an undate command in DB2 ver 7 command center but I keep getting a mad error. My command is the following...

update admin.lja_split
set spl_pol_trans = '01'
where spl_fy = '02' and spl_campaign = '0083' and spl_split = '01'

I get the following error...

SQL0104N An unexpected token "lja_split" was found following "update admin.". Expected tokens may include "END-OF-STATEMENT". SQLSTATE=42601

I've had a look at the online help and my command follows the same syntax. I just can't get it. Any help would be much appreciated.

Cheers,

Chris

 

[blom0344]

Hello Chris,

After connecting to the database with connect statement, you simply refer to tablename in your SQL. So if lja_split is the tablename the SQL should be:

update lja_split
set spl_pol_trans = '01'
where spl_fy = '02' and spl_campaign = '0083' and spl_split = '01'
T. Blom
Information analyst
tbl@shimano-eu.com

 

[ChrisRutherford]

Hello,

Thanks for your reply. I've also droped the admin. off the update command and still get the same kind of error message...

SQL0104N An unexpected token "update" was found following "BEGIN-OF-STATEMENT" Expected tokens may include "EXPAND". SQLSTATE=42601

Cheers,

Chris

 

[blom0344]

Are you sure you are trying to update a table? What is its schema name? If schema name exists as user, try to connect through this authorization id.....
Use ";" to end the SQL statement if you haven't set this already as a fixed SQL ending in command center....... T. Blom
Information analyst
tbl@shimano-eu.com

 

[ChrisRutherford]

Hello,

Thanks again for your reply. The object lja_split is definitley a table with the schema name admin. I've gone into Control Center and verified this.

I can perform a normal Select statement with the same where clause and it return's what it should, the one row that needs updating.

I've put the ; at the end of the SQL statement and this is the error I get back...

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "lja_split" was found following "update admin.".
Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601

Go figure. Not too sure what to do from here.

Cheers,

Chris

 

[blom0344]

Chris,

I am about out on suggestions. Does the authorization id have the update privilege granted? If not i would expect a totally different type of error message, but who knows....
T. Blom
Information analyst
tbl@shimano-eu.com

 

[stoggers]

Hi,

I think you will find that it is because your command centre is not configured for multi-line SQL.

Select Tools->Tools Settings->General and select the box 'Use statement termination character' with ; in the next box.

Regards,

Stoggers

 

[blom0344]

Next time read prior postings to a thread, I mentioned that in the second reply.... T. Blom
Information analyst
tbl@shimano-eu.com

 

[ChrisRutherford]

Hello,

Thanks Stoggers that worked perfectly, thanks. T. Blom, thanks for your help but you never directed me to the tools menu to fix this issue, you mentioned to put a ; at the end of the statement which I did but this did not work. I make a point of reading all information.

Cheers,

Chris

 

[blom0344]

"if you haven't set this already as a fixed SQL ending in command center......."

Hello Chris,

The most important thing is that your problem is solved,I thought that the above hint should have dealed with the issue Stoggers make his remark on...

No offence intended .......... T. Blom
Information analyst
tbl@shimano-eu.com

 

[ChrisRutherford]

Thanks T Blom for your reply, much appreciated.

Cheers,

Chris