Solved

script to update records

Posted on 2011-02-22
7
293 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

735 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