Want to create SQL query for updating records

I am using the Query below to extract data from  a table:

select 
refnbr,replace(reverse(left(reverse(url),charindex('%',reverse(url))-3)),'%',''),url
from xep_aptran where url <> ''
 order by refnbr

The data that is extracted when I run the query is as below

Refnbr      (No Coloum Name)             URL
000199    CAON00000013 
https://www.ineedafile.com/MEDCO/popup/view.asp?TRMD8423%09sj60sdj2c%09CAON00000013
000200    CAON00000011 
https://www.ineedafile.com/MEDCO/popup/view.asp?TRMD8423%09sj60sdj2c%09CAON00000011

What I really want to do using this result set is to create a query as below 
using the result set of the query above

Update apdoc set user1 = (No Coloum Name Colum Result) where refnbr = 
'Refnbr coloum Result)


So I really want to update the apdoc table based on the results of my first 
query.

I would really apprecaite some help on this

Thanks

Sam C
 

0
Sammy
3/25/2010 6:23:22 AM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
421 Views

Similar Articles

[PageSpeed] 35

On Thu, 25 Mar 2010 02:23:22 -0400, "Sammy" <s_commar@hotmail.com>
wrote:

>I am using the Query below to extract data from  a table:
>
>select 
>refnbr,replace(reverse(left(reverse(url),charindex('%',reverse(url))-3)),'%',''),url
>from xep_aptran where url <> ''
> order by refnbr
>
>The data that is extracted when I run the query is as below
>
>Refnbr      (No Coloum Name)             URL
>000199    CAON00000013 
>https://www.ineedafile.com/MEDCO/popup/view.asp?TRMD8423%09sj60sdj2c%09CAON00000013
>000200    CAON00000011 
>https://www.ineedafile.com/MEDCO/popup/view.asp?TRMD8423%09sj60sdj2c%09CAON00000011
>
>What I really want to do using this result set is to create a query as below 
>using the result set of the query above
>
>Update apdoc set user1 = (No Coloum Name Colum Result) where refnbr = 
>'Refnbr coloum Result)
>
>
>So I really want to update the apdoc table based on the results of my first 
>query.
>
>I would really apprecaite some help on this
>
>Thanks
>
>Sam C
> 
Hi Sam

Try something like:

UPDATE a 
SET user1  =
replace(reverse(left(reverse(x.url),charindex('%',reverse(x.url))-3)),'%','')
FROM  apdoc  a
JOIN xep_aptran x ON a.refnbr = x.refnbr AND x.url <> ''

I think that this may also work but I have not checked i

UPDATE a 
SET user1  =
replace((eft(x.url,LEN(url)-charindex('%',reverse(x.url))-3)),'%','')
FROM  apdoc  a
JOIN xep_aptran x ON a.refnbr = x.refnbr AND x.url <> ''


John
0
John
3/25/2010 7:34:25 AM
John
Cant thank you enough. This worked great. Thanks so much again.
Sam C

"John Bell" <jbellnewsposts@hotmail.com> wrote in message 
news:d14mq59h2183mlnqbo2rnlc74dkbnj315e@4ax.com...
> On Thu, 25 Mar 2010 02:23:22 -0400, "Sammy" <s_commar@hotmail.com>
> wrote:
>
>>I am using the Query below to extract data from  a table:
>>
>>select
>>refnbr,replace(reverse(left(reverse(url),charindex('%',reverse(url))-3)),'%',''),url
>>from xep_aptran where url <> ''
>> order by refnbr
>>
>>The data that is extracted when I run the query is as below
>>
>>Refnbr      (No Coloum Name)             URL
>>000199    CAON00000013
>>https://www.ineedafile.com/MEDCO/popup/view.asp?TRMD8423%09sj60sdj2c%09CAON00000013
>>000200    CAON00000011
>>https://www.ineedafile.com/MEDCO/popup/view.asp?TRMD8423%09sj60sdj2c%09CAON00000011
>>
>>What I really want to do using this result set is to create a query as 
>>below
>>using the result set of the query above
>>
>>Update apdoc set user1 = (No Coloum Name Colum Result) where refnbr =
>>'Refnbr coloum Result)
>>
>>
>>So I really want to update the apdoc table based on the results of my 
>>first
>>query.
>>
>>I would really apprecaite some help on this
>>
>>Thanks
>>
>>Sam C
>>
> Hi Sam
>
> Try something like:
>
> UPDATE a
> SET user1  =
> replace(reverse(left(reverse(x.url),charindex('%',reverse(x.url))-3)),'%','')
> FROM  apdoc  a
> JOIN xep_aptran x ON a.refnbr = x.refnbr AND x.url <> ''
>
> I think that this may also work but I have not checked i
>
> UPDATE a
> SET user1  =
> replace((eft(x.url,LEN(url)-charindex('%',reverse(x.url))-3)),'%','')
> FROM  apdoc  a
> JOIN xep_aptran x ON a.refnbr = x.refnbr AND x.url <> ''
>
>
> John 

0
Sammy
3/25/2010 10:19:18 AM
Reply:

Similar Artilces:

Select records from query using added checkbox
Hallo, I have a subform in which I show records resulting from a query displaying certain data from two tables. I would like to add a checkbox to every entry of this query, so the user can select the query-records he wants to use to perform additional tasks with. E.g. Say the query collects data on persons and their jobs based on certain criteria. The user must be able to (de-) select the persons he would like to leave out of a report that will be made once the user has decided on the persons. Also I would like to have all the checkboxes initially checked, so the user only has to deselect s...

Update
What is Happening? This message is to inform you that Microsoft will soon begin discontinuing newsgroups and transitioning users to Microsoft forums. Why? As you may know, newsgroups have existed for many years now; however, the traffic in the Microsoft newsgroups has been steadily decreasing for the past several years while customers and participants are increasingly finding solutions in the forums on Microsoft properties and third party sites. This move will unify the customer experience, centralize content, make it easier for active contributors to retain their influence, mitiga...

Parameter query
Hi I make parameter query in excel 2003. The query works fine and automaticly update. I used a cell as parameter. He qurey is on sheet RuweQrYGegevens And the parameter cell on Sheet basisinfo. So far no problem. On sheet basisinfo cell d5=RuweQrYGegevens!c7 That works fine. But when i put no vallue in the parameter cell and press enter The query change the formula in d5=RuweQrYGegevens!verw and i must d5=RuweQrYGegevens!c7. what is wrong. Greetings Glenn ...

Create a page as an insert
I am creating a newsletter to be printed on 11x17 stock, to be folded to 8.5x 11. I have run out of space and need to have an insert page to be enclosed.(Insert to be 8.5x11 single sheet printed both sides) Do I need to create a new file for just the insert or is there a workaround in Publisher that I haven't figured out? Because the Newsletter + insert page will be emailed as PDF, I really do not want recipients to have to fool with 2 files. Thanks, Jim Is the insert-page part of the general flow or is it totally separate from the rest of the publication? AIUI you are both emailing ...

want questions
Hello Everyone, I want some set of questions on Word-2007 to check competency level of the user. Please help. -- Ananta Gupta Ananta, please refer to my other 2 postings: just check out the WORD part. If my comments have helped please hit Yes. Thanks. "Ananta Gupta" wrote: > Hello Everyone, > > I want some set of questions on Word-2007 to check competency level of the > user. Please help. > > -- > Ananta Gupta "trip_to_tokyo" wrote: > Ananta, please refer to my other 2 postings: just check out the WOR...

I want more!
I need to extend a form that aleady has some 100 lines. For instance, one column has the date, but only until tomorroq. How do I create more lines based on the same formula? Thank you JB I usually select the last row in the range and copy it down a few rows. If you're using xl2003, you may want to look at Data|List. And if you're using xl2007, this feature has transformed into Tables. (Data tab on the ribbon|Data tools Group|What if Analysis|Data table) JB wrote: > > I need to extend a form that aleady has some 100 lines. For instance, one > column has the da...

SQL RS
Yesterday, I installed SBS2003, SQL2005. In the process of installing CRM 3.0, I came across this error, and I am at a loss as to what I need to do. Any Help??? "Activating SQL Server Reporting Services failed. Specified reporting services report server URL https://server.domain.com/reportserver cannot be resolved to an IP. The requested name is valid, but no data of the requested type was found." Did you install reporting service when you install SQL 2005? If so, when you install CRM, you need to pick "connecting to existing reporting service server". CRM will ...

Updating Distribution Lists
Just upgraded with a new computer from Outlook XP to Outlook 2003. All Contacts and info appeared to migrate very well. Only problem now is with Distribution Lists which also migrated. But when I try "Update Now," it will not recognize the Contacts folder and wants to convert all recipients to non-Contact status. That means when I update a Contact with a new e-mail address it won't be updated in the Distribution List. I already tried. Any thoughts on how to get the list to recognize the Contacts? DL's never survive an import. You'll have to recreate them if you w...

I don't want a page of the same label, I want one of each...
I can't find where to tell Pubisher that I want one of each label and not a page of each label. I'm using Publisher 2003 (Office 2003) I see a post that says to enter "<<Next Record>>" but Publisher doesn't see this as a field, just as text. This can't be that hard, but I can't figure it out... Any help would be appreciated. Kelvin I see that it prints correctly, but the print preview displayes it wrong.... I think I have it figured out... "Kelvin" <someone@domain.com> wrote in message news:OSpLwThUIHA.5508@TK2MSFTNGP04.phx....

Need query of all records based on month
How can i create a parameter query in the query grid to ask for all records based on the month. example: i want all records for May 2007. any ideas? In article <21EC5C38-AB39-4AA1-88D8-7C9627A947FE@microsoft.com>, jackle@discussions.microsoft.com says... > How can i create a parameter query in the query grid to ask for all records > based on the month. example: i want all records for May 2007. any ideas? > say you have a date column in your table Field: DatePart("m",[Orders].[OrderDate]) Criteria: DatePart("m",[Enter date:]) In article <21EC5C38-AB3...

Text want wrap in text box
I can't get the text to wrap in a text box, word wrap is set to true, I put some text boxes on a sheet today and the text will not wrap, I did this yesterday and it worked fine, I can copy the ones from yesterday and paste them on a new sheet and they work, any ideas? I have rebooted but this did not help. Using Excel 2000 (9.0.6926 SP3) Thanks Paul B If you're referring to a Control Toolbat text box make sure that the MultiLine property is True. -- Jim Rech Excel MVP "Paul B" <pbridgesnews@uga.edu> wrote in message news:%23ovLUkuLEHA.2456@TK2MSFTNGP12.phx.gbl......

Want to Add more Sub description Field
How I can add more sub description field like 1,2,3,4,5 regards Saleem Suri Salaam Saleem, You have only limit up to 3 fields in RMS 1,2,3 sub-description also you can use the extended description and there is main description for the item. so Altogether you have upto 5 description limit and I think that is enough. Let me know if this won't fulfill your requirements else use the Item Notes for more description. Rate if like Regards Akber "Saleem Suri" wrote: > How I can add more sub description field like 1,2,3,4,5 > > regards > Saleem Suri Salam Akbar ...

trendline equation updates
I am using copying a chart, which has trendlines dispalying the equations, from another spreadsheet and updating the chart for the new data. I noticed some of the equations were not right. Please be noted that these equations were formated for text size and colour. Could anyone suggest why the equations are not right and is there a way to get it right automatically? ST - You may need to provide more details before anyone has suggestions: (a) What version of Excel? (For example, I mostly use Excel 2003 SP3 and Excel 2007 SP2.) (b) Specifically, how are you "updating the cha...

How to create multiselect fields in CRM.
Group, I've got some question about customization in Microsoft CRM. We have a need to create multivalued fields in CRM. Example: the account is dealership and we need to capture the list of makes and models it sells. Pick list does not work, because it can let you select only one value. Looks like we are going to have to create new entity, but schema does not let us do it. Please let me know how to do it? Thanks, Sergey Zelvenskiy Sr. Software Engineer Encore Development sergey.zelvenskiy@encoredev.com Sergey None of the controls in CRM support multiple picks. I would recommend you l...

Web query
i'am trying to get data to a excel from a web site which has a password to enter. web query option dose not allow this. any ways to overcome this problem?? ...

How do I create custom size labels (not Avery)?
I've been given Office Max labels that are 1 X 2.8125 with no margins. They sit 3 across and 11 down for a total of 33 labels/page. I've not found an Avery product that matches. Older versions of Publisher allowed one to create such labels, but I can't find a way in the latest Office version. rlawr <rlawr@discussions.microsoft.com> was very recently heard to utter: > I've been given Office Max labels that are 1 X 2.8125 with no > margins. They sit 3 across and 11 down for a total of 33 > labels/page. I've not found an Avery product that matches. Old...

I want to add a sound event when the cursor bumps left margin
I want to know (via an event sound) that my cursor movement has bumped the left edge of the spreadsheet. I am entering thousand of rows of data, and it would help if there was a event sound when I returned the cursor to the left edge. I have this feature in 123, and find it helpfull. Put this in the sheet module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static ColOld As Integer If Target.Column = 1 And ColOld > 1 Then Beep ColOld = Target.Column End Sub -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "m...

Excel wants to create a page break in every cell!
After saving revisions in an Excel worksheet, I get a popup message that states "Margins do not fit page size". After clicking "ok" (because this is my only choice) a page break is automatically created in ever cell. At times(depending on the sizeof the work sheet) it can be over 100 pages with a page size 14% or smaller. Why does this happen, and how can I avoid it? I saw this suggestion in a different forum. Try closing excel. Clean up your windows temp folder Windows start button|Run type: %temp% and hit enter Clean all those files that you ca...

How to create a CMYK colour image from an Excel graph?
...

Open Access query in Excel with 'save formatted'
I have a query in access which is doing a group by on one field and a sum on another field. The query runs fine and I get one ID (group by) and a total (sum). I have a macro which will open the result of this query in Excel. When this runs all of the data (no group by or sum) is being sent to Excel. If I were to do a file export on the query and click on 'save formatted' then I get exactly what I want but using the macro to do the export is not allowing me to 'save formatted' so I am ending up with all of the data in my Excel spreadsheet. I am wondering if there is a wa...

Quick Campaign arent create activities
I have created a Quick Campaign for a few number of Contacts, with a phone call activity for every contact. I have finished with the steps, and concluded the Quick Campaign, but the activities are not created. What am I doing wrong? I am using CRM 4.0 with roull up 3 Hi Katy, Have a look in System Jobs under settings There will be jobs created for the Quick Campaign to create the activities it's most likely the jobs have not completed. If they are failing you should be able to get a error message that may help further with troubleshooting this. Thanks Wayne "Katy" &...

can you create a chart in excel with alphanumeric data?
any ideas please? we have data as follows: y1 y2 y3 y4 name1 1 2a 3 4b name2 2a 2 1d 3a which needs to go in a chart but the alphanumerics (4b etc) keep being left out...) help! Do the Alpha numerics equal a value? or are they a lable of a point? If they equal a value, then charting can be done. Is they are a lable, can they be represented by a value, if not you may have to make a drawing rather than a chart. Charts plot values. "Pugwyrm" wrote: > any ideas please? we have data as follows: > > y1 y2 y3 y4 > name1 1 2...

Error message when sending an update to a meeting
When doing an update to a meeting request, I am getting "unable to send to one or more delegates." I am a delegate for another's calendar doing the update. All the delegates for the attendees are valid on the server (listed in GAL). I searched the KB for any information on this error and I came up empty. Does anyone have any ideas what the cause might be? Thank you. ...

Creating a work book
How do I go about creating a work book in XL from a template? I need to keep invoices & day books but dont want to re-do a different spread sheet each time, I guess there must be an automatic way to acheive this? Open a new workbook. Format and set it up the way you like, including number of sheets, formulas, Titles and all other stuff you want to see. When you get it as you like ready for data input File>Save As>File Type>ExcelTemplate(*.XLT) Nmae it whatever you wish. Like yourname To use this Template, hit File>New...Template>On my Computer. Select your Template ...

WANTED
WTB: I BUY SOFTWARE - CHECK AROUND - YOU PROBABLY HAVE SOME OF THE BELOW TO SELL TO ME. SEE A LIST OF SOME OF THE SOFTWARE THAT I AM BUYING! I AM BUYING ALL OF THE BELOW. THE MORE QUANTITY THE BETTER! I am looking to buy large quantities of Windows 7 Professional Upgrade Dell DVDs with unactivated COAs included in a SEALED package I am looking to buy quantities of SYMANTEC NORTON ANTI VIRUS, NORTON INTERNET SECURITY AND 360 OEM AND RETAIL BOXES I will buy 2010, 2009, 2008, 2007 and 2006. If you have good quantities setting around and no longer need send me detai...