Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


script to update records

Posted on 2011-02-22
Medium Priority
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
Question by:Matthew34
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
LVL 15

Expert Comment

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'

Expert Comment

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


Accepted Solution

rushShah earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 15

Expert Comment

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

Author Closing Comment

ID: 34957960
LVL 41

Expert Comment

ID: 34957961
try this.
   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

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%'

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

604 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