Solved

script to update records

Posted on 2011-02-22
7
289 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
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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 40

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:ewangoya
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 48
statistics before and after huge DEL/INS 3 16
find all columns in a database with name like... 4 31
Query 14 0
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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 …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now