script to update records

Posted on 2011-02-22
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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL trigger 5 49
How come this XML node is not read? 3 51
Linked Server - SP with Param to VIew 7 29
Can a Trigger trigger a Trigger? 4 46
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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