update query help 07-14-10

sql 2000

i have three tables

1- create table mytab(id_no varchar(5), Counter smallint))
2- create table Rectab(id_no varchar(5), refno smallint))
3- create table Lib_tab(refno smallint)

insert into mytab values(1,null)
insert into mytab values(2,null)
insert into mytab values(3,null)
insert into mytab values(4,null)

-- Rectab has duplicate values

insert into Rectab values(1,1)
insert into Rectab values(1,1)
insert into Rectab values(1,2)
insert into Rectab values(1,2)
insert into Rectab values(1,2)
insert into Rectab values(1,3)
insert into Rectab values(2,1)
insert into Rectab values(2,1)
insert into Rectab values(3,2)
insert into Rectab values(3,2)
insert into Rectab values(3,2)
insert into Rectab values(3,3)
insert into Rectab values(4,3)
insert into Rectab values(4,3)
insert into Rectab values(4,2)
insert into Rectab values(4,1)
insert into Rectab values(4,1)

---Libtab Library table

insert into Lib_tab values(1)
insert into Lib_tab values(2)
insert into Lib_tab values(3)
insert into Lib_tab values(4)
insert into Lib_tab values(5)

i want to update mytab in manner that i want to get distinct count
(distinct id_no,refno)  of rectab in counter column of my tab, vALUES of
refno MUST EXIST IN Lib_tab e.g out put should like

select * from mytab

id_no  counter
1        3
2        1
3        2
4        3

what will be the update query


*** Sent via Developersdex http://www.developersdex.com ***
7/14/2010 12:08:22 PM
sqlserver.programming 1873 articles. 0 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 34


Similar Artilces:

Check that corrective update from the MS Corp.
--saljbnwyqhdb Content-Type: multipart/related; boundary="bxmtmdlub"; type="multipart/alternative" --bxmtmdlub Content-Type: multipart/alternative; boundary="gznbhdcpjj" --gznbhdcpjj Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "September 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to help protect your compute...

Help with Advanced Budget
I am currently trying to setup an advanced budget. I go thru all the motions in setting up my paycheck and all of the categories in which I am budgeting for and the amount for which I am allocating. The problem I have is wheni go over to the bills tab and enter a bill, the amount on that particular category in the budget goes up throwing the budget off. I have tried everything i know and nothing is working. Can anyone help me with this please? Here's the deal: AB by design and code makes the assumption that everything in Bills is part of your budget. You need to enter the bills...

Pie Charts #10
I need a pie chart to show quarterly data for 5 different departments. Each quarter will display in it's own pie chart. Each of the 4 quarterly pie charts will be sized proportionately to the year end total. A fifth pie chart will display the TTL data for each department. Is this at all possible? Is there a better way to show such data? Qtr 1 Qtr 2 Qtr 3 Qtr 4 TTL Dept 1 10 30 20 15 75 Dept 2 15 25 25 20 85 Dept 3 20 20 15 30 85 Dept 4 25 15 8 30 78 Dept 5 30 10 19 35 94 100 100 87 130 417 Thank you in advance! V On Aug 6, 3:57=A0pm, valerie <vgar...@carletonls.com> wrote: &...

Approach to Access 05-02-07
I have a client that has a db in Lotus Approach 9.5 but wants to convert to Access 2003. The existing db has a number of forms, etc that would be a pain to recreate. Is anyone aware of a conversion utility or method that would save a redesign from scratch? -- ------------------------------------------------------------------------------- This message has been checked for all known viruses. The information contained in this e-mail and any attachments is confidential and may be the subject of legal, professional or other privilege. It is intended for the named addressee only and may no...

Update Query with Parameter
I have an Update Query that asks for 3 fields with parameters. The first parameter [ID] is the criteria in the query to identify which record we are updating. The second and third parameters (city and state) are sending the new information to the table. However, sometimes I only have a city that needs to be updated where the state needs to stay the same as what is already in the table. If I don't fill in the 3rd parameter, the state is deleted from the table. How do I get around this without having to type the state in when the parameter prompts me? Thanks in advance....

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...

Query combining the tables
cons dcity dst dzip ocity ost ozip e f jk 7789 fg cd 989 c o lk 970 sf9 cdf 9890 cdd yf mh 979 hgg mkhi 7699 cons dcity dst dzip ocity ost ozip a d ak 560 b c 789 b e ck 869 de ef 970 c o lk 970 bh mk 976 the output qery should combine data from both tables, should include all rows from both tables and should have the following fields.------ the data should be combined from both tables across consignee, dcity, dstate and dzip fields cons dcity dst dzip table1.ocity table1.ost table1.ozip table2.ocity t...

Formula Help #26
How do I enter a formula that counts based on two different criteria. I column A I have names and in column B I have dates or blank cells. need a formula that counts if there is a date in a specified cell i column B. The formula I have now is =SUMPRODUCT(A1:A12="Scratch")*(B1:B12="?????")) What would I put in the ????? to make it count if the cell has a date -- Message posted from http://www.ExcelForum.com The only thing I can think of would be a two step process. In C1, us the formula = if(and(isnumber(B1),A1="Scratch"),1,0), and so on dow the column. Th...

filter question 04-26-07
yeh sorry. the query is just based on another query with a further filter for the day in it. The table is very simple, Duration is just stored as a number. The records are just timed events for the day.... A grace period is name i made to describe the 2 hour limit which im tring to filter the records apart from the rest of the records which dont fit this criteria, (over 2hrs long, or the 2hrs had been used). We need to treat these latter records (non-grace...) different, so any way of me tagging them somehow is what im looking for. I hope thats enough relevant info for a real-database...

Workflow Rule
Hi there, I'm new to Microsoft CRM. We've just installed v3.0 and I'm trying to create my first workflow rule using the Workflow Manager. When a new account is created I want an email notification to be sent to the account owner. Here's what I've done: In the Entity Type list, I selected Account In the view list, I selected Rule On the file menu, I selected New In the Event list, I selected Create To insert a condition, I chose Check Conditions, then I selected Check Entity Condition Then I selected: If Account <owner> not null then e-mail to <owner> ...

Reconfigured tab key help
I have a user who performed some unknown key combination and reconfigured her tab key function. Instead of moving 1 cell at a time to the left hers moves 1 screen to the left. I have been unable to determine what combination she performed and can not return the tab key to the orginal state. If anyone knows what I would need to do to return her to her original state please let me know. Hi Try Tools / Options / Transition / Transition Navigation Keys check box. Andy. "Gary Dennis" <gdenni@acxiom.com> wrote in message news:1e4501c3ff95$ba3c4d70$a601280a@phx.gbl... > I...

Help Please
High I have one report showing Patients data like "country"diagnosis,etc" the report will order records by country and there is a header for the country. at the buttom of the report i want to put a summary like this country No of Patients country 1 10 country 2 2 etc how can I do this , do I need to generate a subreport, and if so , where i should place it , in the report footer or in the details section. actully , I have tried putting a subreport in the rpt footer , but un...

Sql Server / Access query source code
Is there any compatibility between the queries from Access and Sql Server? Like I have a system that needs to migrate the database and the queries from the source code, I can use DTS to migrate de database from Access to Sql Server but what about the queries? Will Sql Server understand them or I'll have to change it manually? The answer is, it depends. Some query types are not supported, but straightforward SELECT queries are essentially the same in Access-SQL and Transact-SQL. However, if you use expressions, VBA functions, form references or other Access features in your quer...

can anyone help me on directshow filters
can anyone help me on directshow filters Hi goru, >can anyone help me on directshow filters Yes, there are a lot of tutorials out there in the www and the MSDN Library with DirectX Reference can help you: http://www.google.de/search?num=30&hl=de&newwindow=1&safe=off&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=DirectShow+Filter+tutorial&spell=1 Download SDK with Reference: http://msdn2.microsoft.com/en-us/directx/default.aspx Direct Show 9.0: http://msdn2.microsoft.com/en-us/library/ms783323(VS.85).aspx DevMasters.NET: Great List of Engines and Tuto...

The form required to view this message cannot be displayed. Contact your help ad
I'm trying to open email that contains forwarded email's. I'm using Outlook 2002 w/SP-2 and XP Pro on clean installs. I can save the attachments but would like to just display them if possible. ...

Help-import from win98-outlook express 4
I hope someone can help. Situation: Sector failure on an old HD, win98 installed. New HD for boot with WinXP Pro installed. I can read parts of the old HD and would like to import the mail and address book into either outlook or outlook express. No matter what I try with the file->import selection in either Outlook or Outlook express on XP, I cannot import these important files. I have looked through the KB files and followed all suggestions. Does anyone have any ideas? Thanks, B0b ...

automatically update data validation selections
I've restricted user enter for certain cells to a named range. Fo example, the named range includes: Apples, Oranges, Bananas. Let's say a user selects "Apples". Then, the list is updated so tha "Apples" becomes "Green Apples". I want what the user selecte ("Apples") to now automatically reflect "Green Apples". I canno figure out how to accomplish this without using a combo box - but really don't want to use a combo box - just keep the user entry area a cells. Any suggestions with no code or a minimum of code are much appr...

Help!, Adding Custom Forms in CRM
HI Mike, I guess, you have very good exposure to the forms customization in CRM. Could u please help me with this issue. I have a list of products in a seperate database outside CRM. The quantity on hand field in that database is real. Now what I really need is to have a button or toolbar item on the OrderProducts form to Check for availability of the product. I do have the product GUID in the external table. I will have get the currently selected product's guid from orderproduct screen and pass this to a custom aspx page. This custom aspx page will find the available qty and sh...

JET dB Newbie Needs Help
Hello all, I'm haveing a bit of a problem with writing to a jet dB and looking for a little help. The problem is that for some reason data is sudenly being written to the end of the new data which is appearing BEFORE the older data. It is as though JET does not recognize the fact that the older data even exists. Each time a record is added it writes it to the end of the more recent data and pushes down all the previously existing data. This is how I open the dB and recordset: (please excuse word wraping) Set eScalperDB = OpenDatabase("C:\Documents and Settings\My Databases\eScal...

Using VBA does anyone know how to retrieve the latest Forefront updates date? -- Steve ...

Money 2003's Internet Updates corrupt causes shares transaction
Since Monday (20 Aug 2007), when I do a Internet Updates, my shares transaction records appear to get corrupted. I observed that this seems to happen to shares with a "split". I never have this problem all these years until now. I would appreciate if someone can help. -- Sitt Sen This is the same problem that I am enquring about, and it started on the same date. I do not think that it is corruption. I saw somewhere that it has something to do with "Tickers" having dots or no dots.... But I can't seem to find much more info, yet. "sittsen" wrote: ...

Help with DLL's & CALLBACKS
Hello group, I'm trying to write an app that uses regular DLL's as plug-in's; everything was progressing OK until I needed a way for the plug-in to callback to the client program; I just can't seem to figure out how to do it? What I have: //In the DLL .h #define PLUG1_API __declspec(dllexport) // Type definition for the callback function. typedef bool CALLBACK CallbackFunc(int, DWORD); extern "C" PLUG1_API void CalcResults(CallbackFunc* Callback, DWORD ...

help! what is Error Number: 0x800CCC18?
Account: 'wonderland207/mail', Server: 'pop3.live.com', Protocol: POP3, Server Response: '-ERR command not implemented', Port: 995, Secure(SSL): Yes, Server Error: 0x800CCC90, Error Number: 0x800CCC18 "kmh" <kmh@discussions.microsoft.com> wrote in message news:33D840F0-4173-48AD-8154-13557DEB09B2@microsoft.com... > Account: 'wonderland207/mail', Server: 'pop3.live.com', Protocol: POP3, > Server Response: '-ERR command not implemented', Port: 995, Secure(SSL): > Yes, > Server Error: 0x800CCC90, Error Num...

DST update
Hi, we have updated all servers and exchange servers with updates 926666 and 930241 (http://support.microsoft.com/kb/930241) Since all servers are ok except for the main cluster we use. It has two Mailboxe stores, one is mounting ok and the second one not. When i try to mount it manually i receive that error: You do not have the permissions required to complete the operation on the Information Store. ID no: c1041723 Of course i use a domain admin account enabled that has never had issues before mounting stores. The very strange thing is that it is possible for mailboxes contained in that m...

Error running a link query in a query page in Business Portal
Hi, I am working on Business Portal 4.0 and created some custom entities and created a query page. Transactions and CodtCodes were two of my custom entities. When i run Transactions Query it will show CostCodes link in the links section along with other links.When i click cost codes link in the links section it was showing an error saying "Can't process query (Status=TraverseHyperlink)" when i click details it was showing Microsoft.BusinessFramework.Data.InvalidDataSourceException The key 'JobCost.CostCodes+key' has multiple segments. Some segments of the key ha...