I have added a new field to an established table, and am having trouble figuring out how to populate its values:
Two tables are involved: Jobs and Parts
There is a one-to-one relationship between each JobID and its PartID
Each Part has a PartPrice. Now I have added to the Jobs table a JobPrice field. Whenever a new Job is created, JobPrice takes the current value of its Part's PartPrice. Each Job's JobPrice remains constant for historical purposes, while the PartPrice may fluctuate at my client's whim.
The trouble is that the Jobs table is 10k+ records large, and I need to fill the JobPrice values. I am at a loss. I know how to commit the update one record at a time:
UPDATE Jobs
SET JobPrice = (SELECT PartPrice FROM Parts WHERE (PartID = [the part in question]))
WHERE (JobID = [the job in question])
My SQL knowledge is limited to basic statements that I use in my .NET work, and I rarely create anything in Management Studio more elaborate than what you see above. Many thanks for your time,
Guys, New to SQL and I'm trying to create a new field, entitled “Name� and see all employee names from my "Employee" table using the following format: LastName, FirstName, jobcode, emailaddress and hire date.
after much effort my code is as follows:
CREATE VIEW 'Name' AS SELECT LastName, FirstName, JobID, Email, Hiredate AS Relationship FROM 'Name' Create View 'Name' as Select LastName, FirstName, JobID, Email, Hiredate From EMPLOYEE Group By LastName, FirstName, JobID, Email, Hiredate Order By LastName
But i'm still getting the following errors:
Msg 111, Level 15, State 1, Line 196 'CREATE VIEW' must be the first statement in a query batch. Msg 102, Level 15, State 1, Line 199 Incorrect syntax near 'Name'. Msg 111, Level 15, State 1, Line 200 'CREATE VIEW' must be the first statement in a query batch.
I need to know who is entering data in my SQL database. I use Access Project as a front end and would like to know if it is possible to have an 'ID' field auto-populate with a user NT Login. For example, if user checks a check-box or enters data in field, I would like an ID field next to the checkbox or data field to populate with his user ID.
I have 2 tables in a parent->child relationship. The primary key for the parent table is moving to a new column (that is an Identity). I'd like to relink the tables using the identity field.
Here's the structure: Parent refid int (Identity) - NEW PK orderid char(9) - OLD PK
Child orderdetailid int - PK orderid char(9) - Old link to parent refid int - New link to parent
I've populated refid in the parent. I just need to populate the refid column in the child. I have a VFP background. In VFP I would have looped through the child, found the matching record in the parent, then transfered the data over. I don' t have a clue how to do this in TSQL.
How would I accomplish update just a part of a field? I have 500+ records in a table that got written as \share 01234.jpg, \share 01235.jpg...\share 01734.jpg but the records should have ended with .tif instead. Now the application cannot find the correct image because the extension is incorrect. How can I use the Update statement to just change the extension on these records?
Hi,I have a 2 tables called 1.tblRisk which consists of Ref(pk), subject, status, staff & Dept(fk)2.tblDept which has Ref(Pk) & DepartmentHow do i get it to populate Department, when tblRisk Ref's Dept matches the Ref in tblDept i am using SQL Server 2000best regards
I am fairly new to SQL programming and would like to see if you can provide me with some direction on a small project I would like to accomplish. I have experience working with databases (mainly in with different versions of FoxPro) and reporting with Crystal reports, etc. but not much with SQL.
The project that I would like to work on has 2 SQL DBs and entails looking in a database for empty CLASSCODE fields, populate these fields from a “storage� database that contains un-used CLASSCODE codes and delete the assigned codes from the storage DB or mark them as used possibly the book code.
The main DB called books has the following relevant fields, bookid, bookname, CLASSCODE (besides a number of other fields which are irrelevant to what I need to do). The “storage DB called classcode has 3 fields, CLASSCODE, Used (single character intended to contain either a Y or NULL) and date assigned. The original idea was to put a Y in the used field and insert the date the code was assigned to a book, but this is not entirely necessary, we have 90k unused codes and it really does not matter if they are simply deleted from the DB.
My idea is to create a script to accomplish this that I can run periodically when new books have been added to the DB. I can setup the update routine (something like the list below) but what I am stuck with is trying to do the following:
Pick the first record (classcode) from the classcode DB Go to the books DB and update it with this classcode Go back to the classcode DB and mark the used record with a Y in the Used field Loop though the books DB and perform the same operation until no more records exist with an empty classcode field.
I will greatly appreciate your feedback you can provide.
Mike.
UPDATE [dbo].[Books] SET dbo.Books.Classcode = [dbo].[classcode].[classcode] From dbo.classcode On [dbo].[Books].[classcode]= [NULL]
For example. I have a table with fields named "From" and "To" which track drill hole measurements. When I add a new record I would like it to autopopulate the "To" field with the "From" value from the previous record.
Example Recordset Record 1, From=0, To=500, Lithcode=AB1 Record 2, From=500, To=1000, Lithcode=CR4 Record 3, From=1000<- trying to figure out how to auto populate previous record's from value here
UPDATE SCORESET QUAL_SCORE = ((SCORE - average_score)/deviation_score)*(-0.25) +((accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25))WHERE SCORES.DISABLEMENT_ZIP = v_disablement_zipAND SCORES.EQPMNT_CODE = v_eqpmnt_code; is it possible to use this one query to update the QUAL_SCORE field without using cursor.if SCORE and deviation_score are 0, Then (SCORE - average_score)/deviation_score)*(-0.25) is 0,if accuracy_score and accuracy_deviation_score are 0, then (accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25 is 0.
Hi all, sorry if this is the wrong place to put this.I have two tables, both contain address info. I would like to updateaddress1, address2, city, state, zipcode and country. May be a fewother fields.The table I am comparing to has many duplicates. The linkage betweenthe two table is by ssn. However I have one field that is date stampedas to which is the most current.How do I get the last date stamped record and update the other table?update group1 setaddress1 = c.address1address2 = c.address2city = c.citystate = c.statezipcode = c.zipcodecountry = c.countryfrom main c, group1 gwhere g.ssn = c.ssn and max(lastchanged)I know the above does not work but it is what I am try to do. Cananyone help?TIA!!!!
Was wondering if there is easy anyway to autopopulate a field by pulling a value from a field in the previous record?
For example, I have a table with fields name "Dist_From" and "Dist_To". When I add a new record I would like it to populate the "Dist_From" field with the "Dist_to" value from the previous record.
Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' ) Begin --Adding a sequence of numbers to the ServiceLineID column. SET @id = 0 UPDATE tmpMHPCLMDET SET @id = ServiceLineID = @id + 1; End;
I am working with a vendor application called Cisco Unified Attendant Console - it operates on a Windows server with a SQL express database. The CUPs function of the application needs to reference a "contact" field with only the user portion of the contact's email address - generally, the contact's User ID will match the user portion of their email address, however, for this customer it does not (they use the employee number as the User ID and firstname.lastname as user portion of the email address.
Writing a script to accomplish the following:
The dbo.Contact_Properties table of the ATTCFG database has the following fields that we can work with:  - First_Name  - Last_Name  - Email  - User_Field_2  - Contact_Unique_Ref (appears to be the field that ties all other contact tables together ?)
Is it possible to create a script that could run daily to either, combine the First_Name and Last_Name fields (with a period between) and populate the User_Field_2 field for each user, or populate the User_Field_2 field with everything before the @ symbol in the Email field for each user?
Also, by default the servers that this application is installed on does not have SQL Server Management Studio installed - is it possible to accomplish with PowerShell script triggered from the Windows Scheduler?
hi all, can anyone help me?I am a relative newbie to sql server and I am more familiar withEnterprise Manager than QA. I have made many many access databasesthough. I am making an asp.net application where by there are a setnumber of users, about 80, each one logs in and manages informationwithin their department.To get them started a manager has written 10 different hazards thatwill apply to all of the departments, and he has written consequencesand controls for the hazards. Each department must have thisinformation as each will manage and deal with them differentlyThe hazard information is stored in a main 'hazards' table, and theconsequences and controls are stored in related tables linked by the'hazardID' from the main table to a foreign key 'hazardID' in therelated tablesWhat i want to know is if there is a relatively simple way of using aquery to populate the 10 hazards to each department, and to alsoinclude the related table links, i dont mind renaming the departmentsnames to match each hazard, but i do not want to have to relink therelated tables manuallyIf anyone can give me any advice to get me started i will be incrediblygratefulthank youTable information is belowHazards------------HazardID - identity key fieldHazard - varcharDepartment - varcharConsequences----------------------ConsequenceID - identity key fieldHazardID - FKConsequence - varcharControls------------ControlID - identity key fieldHazardID - FKControl - varchardwight
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
Mike writes "I am trying to learn SQL programming and would like to see if you can provide me with some direction on a small project.
The project that I would like to work on has 2 SQL DBs and entails looking in a database for empty CLASSCODE fields, populate these fields from a “storage� database that contains un-used CLASSCODE codes and tag the assigned codes from the storage DB as used (“Y�)
The main DB called books has the following relevant fields, bookid, bookname, CLASSCODE (besides a number of other fields which are irrelevant to what I need to do). The “storage DB called classcode has 3 fields, CLASSCODE, Used (single character intended to contain either a Y or NULL).
My idea is to create a script to accomplish this that I can run periodically when new books have been added to the DB. I can setup the update routine I am stuck with is trying to do the following:
Pick the first record (classcode) from the classcode DB Go to the books DB and update the first record that does not contain a code with the above classcode
Go back to the classcode DB and mark the used record with a Y in the Used field Loop though the books DB and perform the same operation until no more records exist with an empty classcode field. The existing data would look something like:
dbo.Books
BookidClasscode
A123400001 B123400002 C123400003 D1234 E1234
dbo.classcode
classcodeUsedDate
00001Y5/17/2006 12:00:00 AM 00002Y5/17/2006 12:00:00 AM 00003Y5/17/2006 12:00:00 AM 00004 00005 00006 00007 00008
I need to pick the next classcode.classcode record that does not have a Y in the Used field and apply it to the first books.classcode record that has a NULL classcode record and then mark the used classcode.classcode record with a "Y"
Hopefully this will make some sense, I will greatly appreciate any feedback.
Hi all, I have a problem about a query to update a table
UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288}
where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
Hi. I'm not such an expert in sql and I wanted to update a table's fields only if the value that im going to update it with is not null. How do I do that?
Hello, I have a field in a table that I have to update to the number of the week. Information i use is from another field, which is text in the format like :
01022005 02142005 07082005 11222005 ... 12022005
mmddyyyy.
How to write an update to update empty field with a number of the week based on the value from another field.
if a db field "mynum" of type int contains for example the number 543.Is there a way I can update this field without first selecting the value?so for example set the the current value to +1
in sql server I want to update the "UpdatedDate" field automatically when I change ANY other field in that specific record.it has to be set to the current server time.
I am trying to update a field in a temptable with the count of items in another table. To illustrate, CustomerID=23 and I want the number of occurences in the temp table. Here's the code which DOESN'T work:
INSERT INTO TempTable ( CustomerID, FirstName, LastName, DateAdded, AlbumPicture, LayoutCount ) SELECT Albums.CustomerID, Customers.FirstName, Customers.LastName, DateAdded, AlbumPicture, COUNT(*) FROM Layouts WHERE Layout.customerID = Albums.CustomerID FROM Albums JOIN Customers on (Albums.CustomerID=Customers.CustomerID)
Please take a look at the COUNT line. Here I want to count the occurences of a specific customerid in another table and put in into th LayoutCount field.
SQL server reports "Incorrect syntax near the keyword 'FROM'". Any ideas how to achieve this?
I am updating three fields (defined as decimal(18,4) NULL) in a 1.7 million record table to 0. This table is also very wide (meaning lot of columns). A simple query like this -
Hi, I'm trying to use the Update command to change a field value. I only know the field name at run-time, how do you write this command. This is what I have done which just sets the variable @cFieldName to the value and not the field within the table.
UPDATE [Atable] SET @cFieldName = @aValue WHERE ([Id] = @Id_1)
