Help with MS SQL Server - updating data from 2 tables

Hi all,
Please forgive me, but I'm new to MS SQL Server and I have few questions. First of all I'd like to know if there is a way to update one table with the data from another. Let's say I have 2 tables. TableA table contains the voucher numbers; TableB table contains the image numbers. Is there a way to merge Voucher numbers from TableA table to Image numbers in TableB table if we assume that there is a primary key Voucher Number in both tables If yes- what would be the query structure for that Please forgive me if you think that this question is silly, I have to say I'm not just new to SQL I'm VERY new to it
Thank you in advance.

Posted On: Friday 26th of October 2012 12:05:40 AM Total Views:  288
View Complete with Replies

Related Messages:

query help   (130 Views)
Im trying to do a query to extract all the records from one table that are not in another table. Essentially the unique records from two tables. Not sure of the query though. Any ideas.
Need some really basic help with the usage of SELECT   (89 Views)
Ok I am really new to MySql/php and would need some help with grouping stuff and summing stuff in tables. My Current table looks something like this: Name-Money-Money2 Joe-----1$------7$ Joe-----2$------6$ Jack----3$------5$ Joe-----4$------4$ Jack----5$------3$ John----6$------2$ Mike----7$------1$ However I would want it to look like this (sum all the same names): Name-Money-Money2 Joe-----7$------17$ Jack----8$------8$ John----6$------2$ Mike----7$------1$ At the moment im just using a really basic $query without any group by or sum by stuff: $query = "SELECT * FROM `TableName`"; The question is what should I exactly do with SUMing or GROUP BY to get the table work
Query help..   (136 Views)
Ok, here is what I have. 'Pictures' table with picturename field and keywords field 'PictureNames' table with Name field 'PictureNameEntries' table with NameID and PictureID fields What I have now is a bunch of picture records stored in the 'Pictures' table. Currenlty the keywords field is empty! The 'PictureNames' table holds the names of every person in each of my pictures. Then the 'PictureNameEntries' holds entries that say which name is in which picture. There CAN be multiple names for each picture. So, what I want to do is get all the names for each picture and insert them into the KEYWORDS field in the 'Pictures' table. Here is the query I started: UPDATE pictures, picturenames, picturenameentries SET pictures.keywords=picturenames.Name WHERE picturenames.ID=picturenameentries.NameID AND pictures.ID=picturenameentries.PictureID The problem with this query is that it ONLY GETS ONE NAME per picture. My question is: How can I get all of the entries from the 'picturenameentries' table and insert all of these into my keywords field (separated by a space). Sorry this is so confusing. Anyone help me
MS query help   (152 Views)
This is for a college project: I am trying to average all records of the same ID but also have it do the same for all of the IDs within the same query. Is their a way of doing this From Tables Sow/Gilt ID Litter NumberBornLive
Need help getting value returned by SubQuery   (136 Views)
, I'm doing some work on a database that keeps track of Chicken Catchers and their daily catching totals. There is a couple of tables being used the first is tblCatcher, this holds info about the catchers and how they did on the catch. tblDaily holds the farm information where they caught that day. tblRate is a list of different rates for each type of catch they could perform. Now the following is the SQL that is being called from the ASP page the user will enter two dates and be returned with the corresponding results. The problem is I want to reference a one to many relationship where a field tblCatcher.Trailers will do a SUM of the total weight on the tblTrailers.TotalWeight. The trailers field is a ID field unike to the record; however, a catcher can work on multiple trailers so the trailers field on the tblCatcher.Trailers would look like "223, 224, 225" sq_TotalWeight SELECT SUM(TotalWeight) AS myTotal FROM tblCatcher WHERE RecordID = ([@RecordID]) AND Trailers LIKE ([@Trailers]) sq_tblCatcher SELECT (exec sq_TotalWeight [tblCatcher.RecordID], "[tblCatcher.Trailers]") AS TWeight, tblCatcher.*, tblDaily.rateID, tblRate.Value, tblRate.Type, tblRate.SupervisorRate, tblDaily.Farm FROM tblCatcher INNER JOIN (tblDaily INNER JOIN tblRate ON tblDaily.rateID = tblRate.rateID) ON tblCatcher.RecordID = tblDaily.ID WHERE ((([tblCatcher.CatchDate]) Between ([@StartDate]) And ([@EndDate]))) ORDER BY tblCatcher.LastName, tblCatcher.FirstName, tblCatcher.FileNumber; If you need to to clarify any of these SQL statements please email me.
basic Query help   (120 Views)
I am doing some coursework and need to complete some queries. I'm sure even the best of the best can't quite make queries without knowing the tables so I have: site_details:site_name site_reference:site_grid_reference,site_name* site_boundaries:site_name*,boundary1, boundary2, boundary3, boundary4 site_record:site_record_number,site_name*, site_record_date recorder_details:recorder_id,(more fields like recorder_name, recorder_addres, etc ) surveyour_log:site_record_number*, recorder_id species_details:species_id, (more fields like name, etc) species_record:species_id*,site_record_number The queries I have to complete are: a. A list of Species (no duplicates). b. A list of records for a site after a specific date. c. The number of species found at each site. d. Sites and dates where a particular species was recorded by a specific surveyor. e. List of species that have less than a given number of records within some mapping area (site). I have done the 1st one and the 2nd I'm not sure why it isn't working. For the 2nd query I have: SELECT * FROM site_record WHERE site_record_date>01/01/2003 ORDER BY site_record_date Any help will be much appreciated, I will be working on these all day so I will keep posted as to my progess.
Need help running SQL 2008 R2 server   (117 Views)
I have little computer knowledge. I've been trying to open an E commerce site and stumbled upon Web matrix which helped me install NopCommerce as a web gallery, once I opened NopCommerce and tried to configure it the site told me I needed a SQL server instance name, I then downloaded SQL Server R2 2008, I established servers with access to sharing and set them to windows authentification, I then tried all the instance names and none of them worked, I tried opening ports that didn't work, I added the server exe.s to the allowed programs under the firewall and that didn't work either...I AM STUCK AND RAPIDLY LOSING PATIENCE SOMEONE PLEASE HELP!!!!!!!!
Sp_executesql help   (114 Views)
I'm trying to write a stored procedure that would allow me to edit products. I will be creating various modules that allow certain people to edit a subset of fields in the product table. (e.g. some people can edit only the price while others can edit just the description, etc.) However, rather than creating one stored procedure for each module, I want to create 1 stored procedure, which is used by all. As the modules will have different number of fields, I want to generate the SQL dynamically in the stored procedure. However I'm running into some problems when using sp_executesql. Below is a snippet of the stored procedure: ALTER PROCEDURE [dbo].[sp_updateProduct] -- Add the parameters for the stored procedure here @Product_Id int = null, @Product_LastModified datetime = null, @Product_Name varchar(100) = null, @Product_Description varchar(max) = null, @Product_Active bit = null, @Product_Points int = null, @Product_Price numeric(18, 2) = null, @Product_Keywords varchar(500) = null, @Product_FeaturedHomePage bit = null, @Product_DisplayHomePage bit = null, @Product_Metatitle varchar(100) = null, @Product_Metadescription varchar(500) = null, @Product_Metakeywords varchar(500) = null, @Product_Safeurlname varchar(100) = null AS DECLARE @SQLStatement nvarchar(max) DECLARE @ParamDefinition nvarchar(max) DECLARE @ParamValues nvarchar(max) BEGIN SET @SQLStatement = 'UPDATE Product ' + ' SET ' + ' Product_Name = @P_Name ' SET @ParamDefinition = N' @P_Name varchar(100) ' SET @ParamValues = @Product_Name IF @Product_Description is not null BEGIN SET @SQLStatement = @SQLStatement + ' , Product_Description = @P_Description ' SET @ParamDefinition = @ParamDefinition + N', @P_Description varchar(max) ' SET @ParamValues = @ParamValues + ', ' + @Product_Description END IF @Product_Active is not null BEGIN SET @SQLStatement = @SQLStatement + ' , Product_Active = @P_Active ' SET @ParamDefinition = @ParamDefinition + N', @P_Active bit ' SET @ParamValues = @ParamValues + ', @P_Active = @Product_Active ' END IF @Product_Points is not null BEGIN SET @SQLStatement = @SQLStatement + ' , Product_Points = @P_Points ' SET @ParamDefinition = @ParamDefinition + N', @P_Points int ' SET @ParamValues = @ParamValues + ', @P_Points = @Product_Points ' END EXECUTE sp_executesql @SQLStatement, N@ParamDefinition, @ParamValues If I comment out the 3 if statements, leaving the product name only, it works fine. But if I add the description part, it will error out. I'll get errors like: Incorrect syntax near 'N@ParamDefinition'. and The parameterized query '( @P_Name varchar(100) , @P_Description varchar(max) )UPDATE Pro' expects the parameter '@P_Description', which was not supplied. depending on where I put the N for Unicode. I've been looking for a solution to this for some time and any help would be appreciated. Sincerely, Jason
Query help   (119 Views)
need help in creating an effective SQL query for the scenario below: The user can search on any of the following fields: field1,field2,field3,field4,field5,field6 (asp fields matching to col1...col6 in sql server table) QUESTION:Can there be a combined query to allow this search I have written 2 queries for the case,if col2 !=spaces & is not zero then search for col1 with searchcode='c' & then using this value of col1,search all the other fields If no what is the correct query,my queries are not giving correct results
SELECT help!   (128 Views)
Dear Friends, I need small help. I have 3 tables Grades,CollectionTest and Oraganization. Taking Collection_Test into consideration, i need display invalid records from it. ForeignKeys --------------- collection_test(facilitycode) refers to Grades(orgid) and one more thing is that there is no relationship between collection_test and Organization tables but logically collection_test(facilitycode) refers to Oraganization(orgid). Condition 1 : Take each record from "Collection_test" table,consider facilitycode,gradeid fields.For that facility code u need to take corressponding gradeid and lookup into "Grades" table and check for that facilitycode/orgid , the grade is falling within the range or not.Example, if i consider 1 st record in Collection_test table 430 is my faciltycode and 5 is my gradeid. Now i need to lookup into Grades table for orgid 430 and get the corresponding gradeids from the "Grades" table. (i.e for 430 i will get grades from 1,2,3,4,5,6,7,8,9,10) and check whether collection_test(gradeid) i.e 5 is falling within 1,2,3,4,5,6,7,8,9,10 range or not. If not it is an invalid record and i need to display it. Again we have an Exception here, Eventhough the grade is not falling within the range but taking into "Organization" table into consideration ( this is second level condition checking) check for the orgid in this table( collection_test(facilitycode) refers to Oraganization(orgid)) and for that organization id, if the institution code (instid) is IN (21,41,61,82) then i can ignore that record i.e it is no more an INVALID record and is not displayed in the output. So as a result if u take 2nd record into consideration, i.e 5 431.Though it fails in the first condition but it pass's in the second level conditon and finally no rows is selected for the output. I tried till the first level of the condition , but got stuck for the second level condition. Please help me out.
SQL Query problem... please help   (119 Views)
I do not have much experience with sql, however i do use ms sql 2000 and have set up a product database to use for my website. I am having diffficulty with two things... 1. I am finding difficulty in putting links into my DHTML menu that will return the correct product category from the database. I have set up a database called met_products and need to use this to access the information. Basically, I think that i need to include products.aspseries=.... but i dont know how to put this code in. I have a DHTML menu already setup and ready to link to, however i cannot seem to get my head around the type of code that is needed to do this. 2. Also, I am looking at comprimising a search engine facility for the products only so that the customer can type a keyword in a search and it searches just the products on the sql database. Can you help me with any code ideas on this I know that i will need to use the WHERE function, but I am unsure as to how to put this in and link it to my search.
A little help with a stored procedure- how to update from one table to another   (157 Views)
I want to update an existing table from another table. I would like to take the id# from table1 and insert it into table#2 and also update other columns in table #2 with getdate(). I am doing the following. I tried putting the 'studid' inside the parentheses, but it didn't like this... Any tips would be appreciated. Thank you. Code: ALTER PROCEDURE dbo.updCourseRoster AS INSERT INTO [COURSE ROSTER] (DATEADDED,TIMEADDED) VALUES(GETDATE(),GETDATE()), STUDID = STUDENTSMG.STUDENTID SELECT STUDENTID FROM STUDENTSMG WHERE STUDENTSMG.NOTES LIKE '"XLS SOURCE"'
DISTINCT and GROUP BY help   (91 Views)
Hie Friends, I have this in the table: Car Capacity Price --- -------- ---- BMW 1.8 cc 15000 BMW 2.0 cc 18000 Audi 1.6 cc 14000 Audi 1.8 cc 17000 Audi 1.9 cc 19000 Audi 2.0 cc 20000 VW 2.0 cc 22000 When i do a SELECT * FROM table WHERE price BETWEEN 17000 AND 20000 i will get the following: Car Capacity Price --- -------- ---- BMW 2.0 cc 18000 Audi 1.8 cc 17000 Audi 1.9 cc 19000 Audi 2.0 cc 20000 But how do i only get this: Car Capacity Price --- -------- ---- BMW 2.0 cc 18000 Audi 1.8 cc 17000 i only want to show the lowest price available for the car type between the 17000 and 20000 price range. Cheers!
URGENT - Need help with UPDATE statement   (87 Views)
I have a data table called IMAGES. I wish to search the field PATH for commas and replace them with a spce. I've tried the below but it doesn't work. Code: UPDATE Images SET Path = REPLACE(Path, ',', ' ') Anyone have any ideas I need to get this done today
SQL help needed?   (146 Views)
Hi. In a ASP / Access page I had this code on a dynamic page that inserted "one hit" in the database each time the page was displayed. Like this page has been viewed 100 times. Code: ViewCount is the name of the integer db field that stores the number value. In MSSQL this recordset causes a "0x80040E31 timeout error" and the page cant be displayed. Any suggestions for this error If I remove the "insert one hit in db function" in the recordset everything works ok. Bjorn.
sql INSERT help: how to INSERT from a variable?   (161 Views)
i have a .html file shown below. I just want to get the value from the variable and insert that to by access database. Can you please tell me how to get the value in the variable 'nam' in dbcheck() method. i feel the problems are in the red lines highlighted below in my code. the html code: test jscript
Need help to combine 2 records into one   (243 Views)
Here is the table: CREATE TABLE [tBenefit] ( [emp_obenefit_id] [int] IDENTITY (1, 1) NOT NULL , [empplan_id] [int] NOT NULL , [obenefit_id] [int] NOT NULL , [cost_per_month] [float] NOT NULL CONSTRAINT [DF_EmployeeOBenefitApproved_cost_per_month] DEFAULT (0), [coverage_start_date] [datetime] NOT NULL , [coverage_end_date] [datetime] NOT NULL ) ON [PRIMARY] GO Here is the query: ---------------------------------------------------------------------------- SELECT emp_obenefit_id, empplan_id, obenefit_id, billing_cost_per_month, e.coverage_start_date, e.coverage_end_date FROM tBenefite WHERE e.empplan_id = 4757 AND e.emp_obenefit_id IN ( SELECT TOP 2 emp_obenefit_id from tBenefit where empplan_id = e.empplan_id AND obenefit_id = e.obenefit_id ORDER BY coverage_end_date DESC, emp_obenefit_id DESC ) ORDER BY e.obenefit_id DESC, e.coverage_end_date DESC, e.emp_obenefit_id DESC ---------------------------------------------------------------------------- Here is the result of the query: emp_obenefit_id empplan_id obenefit_id cost_per_month coverage_start_date coverage_end_date --------------- ----------- ----------- ------------------------------------ 31731 4757 84 0.0 2004-09-01 2004-12-31 26635 4757 84 0.0 2004-04-06 2004-08-31 31730 4757 83 6.2 2004-09-01 2004-12-31 31121 4757 83 0.0 2004-04-06 2004-08-31 31729 4757 82 0.0 2004-09-01 2004-12-31 31120 4757 82 0.0 2004-04-06 2004-08-31 ---------------------------------------------------------------------------- I need to construct a query that will get me one recordset for each obenefit_id where cost_per_month of previous (between 2004-04-06 and 2004-08-31) period will not be equal to current period (between 2004-09-01 and 2004-12-31), it they are equal this means that no change has been done to the costs so I do not need it The result of this query has to look like this: empplan_id obenefit_id cost_per_month_prev cost_per_month_current --------------- ----------- ----------- -------------------------------- 4757 83 0.00 6.2 Only obenefit_id = 83 must be displayed because as it is seen in the query results values for 2004-04-06 and 2004-08-31 value for cost_per_month was 0 and for 2004-09-01 and 2004-12-31 is 6.2 (value changed) Thank you for your help
Need help in Configuring ISA server to publish SQL server 2000 to web   (97 Views)
Hi! We want to publish some data from our intranet application to the web. I created a simple connection string within a ASP page which is located on a remote web hosting server: Set conn = Server.CreateObject("ADODB.Connection") conn.Open "Provider=SQLOLEDB;Persist Security Info=False;Data;Initial Catalog=Northwind;Network Library=dbmssocn;UID=sa;PWD=xxxxx;" but unfortunately it gives me this error message Microsoft OLE DB Provider for SQL Server (0x80004005) [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. ---> I already configured our ISA server to listen to port 1433, but still dont work. Do I still need to configure SQL server Help pleaseeeeeee,
multiple left joins help?   (166 Views)
Could somebody tell me what is the secret of being able to write a SELECT statement having mulitple LEFT or RIGHT joins, I seem to get in trouble as soon as I add the second LEFT join, as I am obviously doing it wrong. These are my tables, would somebody mind having a go,or explaining what do i need to be aware of in a case like this emailDetails table ( emailID_PK,emailName,emailText,emailSubject,emailN otificationTypeID) emailRecipients table (emailID_PK, RecipientID, SentToEmail) luEmailNotificationTypes look up table (emailNOtificationTypeID_PK, emailNotificationTypeName)
Need help on database   (157 Views)
Sorry, I'm a newbie out there when it comes to creating database. I currently have an ASP.Net project which requires a database. I spent the past few days thinking about how to create a proper database for the application but to no avail. I think tis thread might be out of point for tis forum, but i am really in need of help. Here goes the storyline of my database: The project is an online project management system, meant for students to submit their deliverables (reports, eg. Minutes, agenda, project monitor chart) Lecturers will access the web application to grade the students submission. The application must allow administrators to add in new subjects. When a new subject is added, the subject leader will set a dateline for a particular module to be submitted. Eg, minutes dateline. Once the dateline is due, the system will inform the lecturers in charge of who/which group did not submit the module. Lecturers and subject leaders can then generate warning letters to be sent via email to the students involved. Lecturers and subject leaders will be able to make announcements through the system as well. A student can either submit his/her work as an individual or as a group. A student will be studying more than 1 subject. A subject will consist of more than 1 module. Every module will have a dateline for its submission. Students can make more than 1 submission for each module. Students belong to different groups/classes for different subjects. For different modules, there will be different fields to be inserted into the database. Example, in the Minutes module, the student will have to be able to key in the objectives, the date of the meeting, the venue, its members, absent members, actual minutes, action by and meeting end time. In the Project monitor chart module, the student will have to key in the week/date, the name of the member, task involved, as well as the status. When a new subject is created, the subject leader will have to specify the percentage of each different module. (Eg, Exams 40%, Term test 15%, and so on.) A subject will also be taught by more than 1 lecturer. So subject leader will specify the lecturers teaching that particular subject. I hope someone out there can help me out on the construction of a database for the above storyline.