Solved

script to update records

Posted on 2011-02-22
7
295 Views
Last Modified: 2012-05-11
I am after a script to update a record in a table by doing the following:-

update puf_ord_diss set (first 3 characters of accrnbri) to 198 where first 3 characters are 998 where the pord_status in table puf_ord_ctl = 'O'

The common field between the two tables is pordnbr
0
Comment
Question by:Matthew34
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 34957893
added convert to varchars in just in case they are numeric fields

update puf_ord_diss
set accrnbri = '198' + right(convert(varchar(max), accrnbri), len(convert(varchar(max), accrnbri))-3)
where pord_status = 'O' and left(accrnbri, 3) = '998'
0
 
LVL 8

Expert Comment

by:rushShah
ID: 34957932
try this,

update p
set p.accrnbri = '198' + right(convert(varchar(max), p.accrnbri), len(convert(varchar(max), p.accrnbri))-3)
INNER JOIN puf_ord_diss p ON puf_ord_ctl c ON p.pordnbr=c.pordnbr
where c.pord_status = 'O' and left(p.accrnbri, 3) = '998'

Open in new window

0
 
LVL 8

Accepted Solution

by:
rushShah earned 500 total points
ID: 34957938
sorry, try this..
update p
set p.accrnbri = '198' + right(convert(varchar(max), p.accrnbri), len(convert(varchar(max), p.accrnbri))-3)
FROM	puf_ord_diss p
INNER JOIN puf_ord_ctl c ON p.pordnbr=c.pordnbr
where c.pord_status = 'O' and left(p.accrnbri, 3) = '998'

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:derekkromm
ID: 34957948
Ahh, sorry, didn't see that these were 2 different tables. rush's 2nd query should work correctly.
0
 

Author Closing Comment

by:Matthew34
ID: 34957960
Thanks
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34957961
try this.
UPDATE pd 
   SET accrnbri = '198' + SUBSTRING(accrnbri,4,LEN(accrnbri)) 
  FROM puf_ord_diss pd 
       JOIN puf_ord_ctl po 
         ON pd.pordnbr = po.pordnbr 
 WHERE LEFT(accrnbri,3) = '998' 
       AND pord_status = 'O'

Open in new window

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34957992

update puf_ord_diss
set accrnbri = '198' + RIGHT(accrnbri, len(accrnbri) -3)
FROM      puf_ord_diss p
INNER JOIN puf_ord_ctl ON puf_ord_diss.pordnbr = puf_ord_ctl.pordnbr
where puf_ord_ctl.pord_status = 'O'
and puf_ord_diss.accrnbri like '998%'
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question