MQSeries.net :: View topic - Update Statement

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Update Statement

Update Statement

« View previous topic :: View next topic »

Author

Message

ravi_sri24

Posted: Sun Mar 04, 2007 10:20 pm Post subject: Update Statement

Voyager

Joined: 11 May 2006
Posts: 83

Hi am updating the data in the DB using the below command in the DB Node

UPDATE Database.MSG_TABLE T SET T.NAME = Environment.Variables.UniqueNAME where T.ID = Environment.Variables.InputUniqueID;

but am getting the error, is this syntax is correct am going in a correct way or not

mvarghese

Posted: Sun Mar 04, 2007 10:37 pm Post subject:

Centurion

Joined: 27 Sep 2006
Posts: 141

Hi,
I belived this syntax is wrong.If you running message broker.Kindly check some db related samples.
Normally SQL we are directly executing in side ESQL..may be passthru fuction...
_________________
Jain Varghese

AkankshA

Posted: Sun Mar 04, 2007 10:39 pm Post subject:

Grand Master

Joined: 12 Jan 2006
Posts: 1494
Location: Singapore

try this

UPDATE Database.MSG_TABLE AS T SET T.NAME = Environment.Variables.UniqueNAME where T.ID = Environment.Variables.InputUniqueID;
_________________
Cheers

au@kosa

Posted: Sun Mar 04, 2007 11:35 pm Post subject:

Centurion

Joined: 04 Jan 2007
Posts: 103
Location: pune

You can also use your existing query inside Passthru function
_________________
Regards,
au@kosa
IBM Certified SOA Solution Designer/Associate

ravi_sri24

Posted: Tue Mar 13, 2007 3:37 am Post subject:

Voyager

Joined: 11 May 2006
Posts: 83

au@kosa wrote:

You can also use your existing query inside Passthru function

can i use the update statement with the AND statement, means can i update the 2 statements at a time by using the update statement

am using the below command but am getting the error while deploying

UPDATE Database.MSG_TRAFFIC_TBL AS T SET RELATIONSHIP_ID = Environment.Variables.UniqueID WHERE T.UNIQUE_ID = Environment.Variables.InputUniqueID AND PROCESS_STATUS = Environment.Variables.ProcessStatus WHERE T.UNIQUE_ID = Environment.Variables.InputUniqueID;

am getting the fallowing error

BIP2432E: (, 72.9

: The correlation name 'PROCESS_STATUS' is not valid. Those in scope are: Environment, LocalEnvironment, Root, Body, Properties, ExceptionList, DestinationList, T.

Please can one suggest me as am fallowing the correct way or not?

ravi_sri24

Posted: Tue Mar 13, 2007 4:19 am Post subject:

Voyager

Joined: 11 May 2006
Posts: 83

ravi_sri24 wrote:

au@kosa wrote:

You can also use your existing query inside Passthru function

IT IS WORKING LIKE THIS, insite the if am uisng the update statement twice is this is the correct way to do?

IF (TRIM(Environment.Variables.RelationshipID) <> '') THEN

UPDATE Database.MSG_TRAFFIC_TBL AS T SET RELATIONSHIP_ID = Environment.Variables.UniqueID where T.UNIQUE_ID = Environment.Variables.InputUniqueID;

UPDATE Database.MSG_TRAFFIC_TBL AS T SET PROCESS_STATUS = Environment.Variables.ProcessStatus where T.UNIQUE_ID = Environment.Variables.InputUniqueID;

END IF;

please suggest me if am wrong

jefflowrey

Posted: Tue Mar 13, 2007 4:22 am Post subject:

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

You have to qualify every name that refers to your database with the database qualifier, which in your case is T.

UPDATE Database.MSG_TRAFFIC_TBL AS T SET T.RELATIONSHIP_ID=Environment.Variables.UniqueID, T.PROCESS_STATUS=Environment.Variables.ProcessStatus where T.UNIQUE_ID=Environment.Variables.InputUniqueID;

Every time you find yourself struggling to figure out how to write a particular ESQL statement, you should go read the Information Center documentation on the syntax of the ESQL statement.

That's what I do.
_________________
I am *not* the model of the modern major general.

ravi_sri24

Posted: Tue Mar 13, 2007 6:10 am Post subject:

Voyager

Joined: 11 May 2006
Posts: 83

jefflowrey wrote:

thanks for your suggesstion jeff, when i use the qualifier that is not worked, that's the reason why i have posted, i think we can't update 2 fileds with in 1 update statement

when i use 2 update statements inside if statment to update 2 fields in the database it's working fine,

mgk

Posted: Tue Mar 13, 2007 6:27 am Post subject:

Padawan

Joined: 31 Jul 2003
Posts: 1642

From the UPDATE statement documentation:

Quote:

The following example updates multiple columns:

Code:

UPDATE Database.table AS T
SET column1 = T.column1+1,
column2 = T.column2+2;

Note that the column names (on the left of the "=") are single identifiers. They must not be qualified with a table name or correlation name. In contrast, the references to database columns in the expressions (to the right of the "=") must be qualified with the correlation name.

_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.

jefflowrey

Posted: Tue Mar 13, 2007 4:41 pm Post subject:

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

mgk wrote:

From the UPDATE statement documentation:

Which obviously I didn't read before posting.

I've gotten very lazy in the last 3000 posts or so.
_________________
I am *not* the model of the modern major general.

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Update Statement

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP