Update a record in multiple table

Question

0.00/5 (No votes)

See more:

Hi All
I have a procedure where I m passing parameter from front end to update three table but it is not it is not updating.

SQL

BEGIN
               UPDATE CNMPNT  SET
               CUSTOMER_ALPHA=@ChangeAlphaPoiner
              WHERE
               CUSTOMER_ALPHA=@aplhaPointer AND
               ltrim(Rtrim(replace(CUSTOMER_ID,char(0),&#39;&#39;))) IN (@CustomerIDs)
          (rtrim(replace(&#39;0045864&#39;,char(0),&#39;&#39;)),replace(&#39;0046677&#39;,char(0),&#39;&#39;))&#39;0045864&#39;,&#39;0046677&#39;) (LTRIM(RTRIM(replace(&#39;0045864&#39;,char(0),&#39;&#39;))),LTRIM(RTRIM(replace(&#39;0046677&#39;,char(0),&#39;&#39;))))           (rtrim(replace(&#39;0045864&#39;,char(0),&#39;&#39;)),replace(&#39;0046677&#39;,char(0),&#39;&#39;))          (ltrim(rtrim(replace(&#39;0045864&#39;,char(0),&#39;&#39;))))--,replace(&#39;0046677&#39;,char(0),&#39;&#39;)))
            &#39;%0045864&#39; =&#39;0046677&#39;
               UPDATE CMAPNT  SET
               CUSTOMER_KEY=@ChangeAlphaPoiner
                              WHERE
               CUSTOMER_KEY=@aplhaPointer AND
               ltrim(Rtrim(replace(CUSTOMER_ID,char(0),&#39;&#39;))) IN (@CustomerIDs)
            (&#39;0045864&#39;,&#39;0046677&#39;) like &#39;0062076&#39;=@aplhaPointer
               UPDATE CUSMAS  SET
               ALPHA_POINTER=@ChangeAlphaPoiner
               WHERE
               ALPHA_POINTER=@aplhaPointer AND
               ltrim(Rtrim(replace(cust_id,char(0),&#39;&#39;))) IN (@CustomerIDs)
               &#39;%0045864%&#39;,&#39;0046677&#39;) =&#39;0062076&#39;
            END

Please help

Posted 7-Mar-11 23:25pm

IndrajitDasgupat

Updated 7-Mar-11 23:47pm

@nuraGGupta@

v3

Add a Solution

2 solutions

Solution 2

I commented the code that does not make any sense (actually will fail if run).

SQL

BEGIN
               UPDATE CNMPNT  SET
               CUSTOMER_ALPHA=@ChangeAlphaPoiner
              WHERE
               CUSTOMER_ALPHA=@aplhaPointer AND
               ltrim(Rtrim(replace(CUSTOMER_ID,char(0),''))) IN (@CustomerIDs)
/*        (rtrim(replace(&#39;0045864&#39;,char(0),&#39;&#39;)),replace(&#39;0046677&#39;,char(0),&#39;&#39;))&#39;0045864&#39;,&#39;0046677&#39;) (LTRIM(RTRIM(replace(&#39;0045864&#39;,char(0),&#39;&#39;))),LTRIM(RTRIM(replace(&#39;0046677&#39;,char(0),&#39;&#39;))))           (rtrim(replace(&#39;0045864&#39;,char(0),&#39;&#39;)),replace(&#39;0046677&#39;,char(0),&#39;&#39;))          (ltrim(rtrim(replace(&#39;0045864&#39;,char(0),&#39;&#39;))))--,replace(&#39;0046677&#39;,char(0),&#39;&#39;)))
            &#39;%0045864&#39; =&#39;0046677&#39;
*/
               UPDATE CMAPNT  SET
               CUSTOMER_KEY=@ChangeAlphaPoiner
                              WHERE
               CUSTOMER_KEY=@aplhaPointer AND
               ltrim(Rtrim(replace(CUSTOMER_ID,char(0),''))) IN (@CustomerIDs)
/*
            (&#39;0045864&#39;,&#39;0046677&#39;) like &#39;0062076&#39;=@aplhaPointer
*/
               UPDATE CUSMAS  SET
               ALPHA_POINTER=@ChangeAlphaPoiner
               WHERE
               ALPHA_POINTER=@aplhaPointer AND
               ltrim(Rtrim(replace(cust_id,char(0),''))) IN (@CustomerIDs)
/*
               &#39;%0045864%&#39;,&#39;0046677&#39;) =&#39;0062076&#39;
*/
            END

Would this work for you?

Posted 8-Mar-11 0:58am

Costica U

Comments

IndrajitDasgupat 8-Mar-11 9:23am

no this is not working for me

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

@nuraGGupta@ · Accepted Answer · 2011-03-07T23:55:00

Solution 1

The snippet you provided seems unreadable to me at-least. But I can suggest another better way of doing the task you are trying to achieve. You can use triggers in your context. Using triggers, you can update the record to next tables if the record has been successfully updated in the current table.

For eg:

SQL

create trigger u_authors on authors for update
as
Insert into log_authors (au_id) select au_id from inserted
go

Hope this helps. :rose:

Anurag

Posted 7-Mar-11 23:55pm

@nuraGGupta@

Comments

thatraja 8-Mar-11 6:03am

/*The snippet you provided seems unreadable to me at-least.*/
No, for me too

IndrajitDasgupat 8-Mar-11 6:03am

Hi
If U don't mind can U give me little more details because I m new to this, so it will help me to go ahead
Thanks for help

@nuraGGupta@ 8-Mar-11 6:56am

Man, you have got google for anything you need and will provide results faster too. For a concept I should tell you that triggers are automatically fired whenever any DML statement is applied on tables. So, "insert command" being a DML statement will fire the triggers for that particular table in which insertion is being made. For more details on how to write triggers, you can search "write triggers in SQL server". That will surely give you required help. Please accept the answer if you found it useful.

IndrajitDasgupat 8-Mar-11 9:20am

Thanks for help I had done what u told