dear sirs,
this is my first contribution at you reputable forum ..
I have a serious problem which is .. I want to create a trigger which runs at the begining of each month .. so I created a table which has a field --> formula --> month(getdate()) i tested it by changing the month in the windows calender and each time i change the month it is updated in the table when I crated a trigger i checked if update(sal_month)
the trigger is as follows
========================================
create trigger tr_update_loan
on salary_month
for update
as
if update(sal_month)
begin
update hr_emp_loan
set payed = payed + one_loan , rest = rest - one_loan
end
=========================================
it returned an error "Column 'sal_month' cannot be used in an IF UPDATE clause because it is a computed column."
I tried to not to use if update() but when I change the date in the calender it is not updated in the other table
can any one help my in this because I'm doing my graduation project
I am currently developing a stored procedure that includes a number of computed fields. Is it possible to reference a computed value, (eg. FLdA), or do I need to CREATE a temp file and then reference the FldA and FldB values. I have simplified my code, it is much more extensive in that there are numerous WHEN clauses attached to each FldA and FldB computation.
SELECT FldA = CASE WHEN .... THEN CurQty * 1.5 WHEN .... THEN CurQty * 1.75 ELSE 0 END), FldB = CASE ..... NewValue = CASE WHEN .... THEN FldA * CurValue WHEN .... THEN FldB * CurValue etc.
Hi all, I have three textboxes in a form view 1- Qty textbox 2- price textbox 3-TotalPrice textbox which value is the result of multiplying the previous two values and then I want to Bind() the third value to the totalprice field in the database how can I do that?? (without making the database field as a computed column)
Okay, newb question alert!!!I created a computer column that is based on the difference between the column start_date and getdate().Does the computed column only update when you update the column or does it change when you select it also?
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
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
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?
I have a table with 3 computed columns, the first two reference a function for there value. The last computed column should be total of the other computed columns, however when trying to write the formula for this column SQL Server rejects it.
Am i correct in thinking it is not possible to reference other computed columns in a computed column's forumla.
My current work around is to call the functions again that the other computed columns use to generate the total, though this seems to me like a performance issue.
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)
I have a table with millions of records, table has three fields: case_id,line_no and notedata field, notedata field is 60 chars long, datatype varchar.for each case_id there could be as many as 2000 line_no meaning 2000 notes. I need to compress these notes into one note by case_no, For example case_no 1 could have 2000 lines of notes but my comressed table shoul have only one line containing all 2000 notes in line_no sequence.
my compressed table contains two fields case_no and notetext, notetext is a text field.
here is the script I am trying to use to accomplish the task but it does not append more than 8000 chars in one case, so my notes are chopping of, how should I do this, please let me know of any suggestions..
Thanks.
truncate table eldoecinotescompressed insert into eldoecinotescompressed (app_code, case_no) select distinct app_code, substring(system_key, 6,8) from eldoecinotes
DECLARE notes_cursor CURSOR FOR select substring(system_key, 6,8) case_no, line_no, rtrim(notedata) notedata FROM EldoECINotes where substring(system_key, 6,8)<>'' order by 1,2; OPEN notes_cursor; FETCH NEXT FROM notes_cursor into @case, @lineno, @note; WHILE (@@FETCH_STATUS = 0) BEGIN
BEGIN TRANSACTION; update eldoecinotescompressed set notetext = (case when isnull(datalength(notetext), 0) >= 0 then substring(isnull(notetext,''), 1, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 8000 then substring(isnull(notetext,''), 8001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 16000 then substring(isnull(notetext,''), 16001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 24000 then substring(isnull(notetext,''), 24001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 32000 then substring(isnull(notetext,''), 32001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 40000 then substring(isnull(notetext,''), 40001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 48000 then substring(isnull(notetext,''), 48001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 56000 then substring(isnull(notetext,''), 56001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 64000 then substring(isnull(notetext,''), 64001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 72000 then substring(isnull(notetext,''), 72001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 80000 then substring(isnull(notetext,''), 80001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 88000 then substring(isnull(notetext,''), 88001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 96000 then substring(isnull(notetext,''), 96001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 104000 then substring(isnull(notetext,''), 104001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 112000 then substring(isnull(notetext,''), 112001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 120000 then substring(isnull(notetext,''), 120001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 128000 then substring(isnull(notetext,''), 128001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 136000 then substring(isnull(notetext,''), 136001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 144000 then substring(isnull(notetext,''), 144001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 152000 then substring(isnull(notetext,''), 152001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 0 then char(13) + char(10) else '' end) + isnull(@note,'') where case_no=@case; commit; FETCH NEXT FROM notes_cursor into @case, @lineno, @note; END CLOSE notes_cursor; DEALLOCATE notes_cursor;
I am tryin t to update the tbl1_ID from the tbl2_ID. How do I do a Update Join that will do a comparison on the param column and value columns so that I could get the correct ID into tble 1. Tbl1 is my destination table and tbl2 is my source. Please Help.
ID Tbl1_ID tb1Param tbl1value
1NULLParam1 0 1NULLParam2 F 1NULLParam3 2 3NULLParam1 0 3NULLParam2 E 3NULLParam3 0 5NULLParam1 0 5NULLParam2 F 5NULLParma3 2
tbl2_ID tbl2Param tbl2value
100param1 0 101param1 1 102param1 2 103param1 3 104param1 4 105param2 E 106param2 F 107param2 H 108param2 HF 109param2 HS 110param2 L 111param2 LS 112param3 0 113param3 1 114param3 2 115param3 3 116param3 4 117param3 5 118param3 6
Here is what Im trying to do if you can understand this.
Update Tbl1 SET tb1ID = B.tbl2_ID FROM tbl1 AS A JOIN tbl2 AS B ON A.tbl1Param + A.tbl1Value = B.tbl2Param + A.tbl2value
this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId.
This is my query that returns the ones are not assigned to and Asset
SELECT DISTINCT EsnId, EsnNumber FROM dbo.ESNTracking WHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))
Loading thousands of records to the DB through bulk insert. There's one field NText which I have left NULL because it will be hard to gen dummy flat file to it.
I have another table which has the Ntext Value which i will want to copy and duplicate to the other table.
what is the way to do it?
simply said i want to update a record with NULL value from one table with NText field with the value from another table..
I would like to append information to a varchar field with an update statement, for example the field currently contains a name (Mark) and I would like to add information to name for business purposes, to update it to Markqw215, is this possible to do with an update statement? Thank you.
I would like to append information to a varchar field with an update statement, for example the field currently contains a name (Mark) and I would like to add information to name for business purposes, to update it to Markqw215, is this possible to do with an update statement? Thank you.
ok, so we have a field in one of our db's, a do_not_synch field. this field has a datatype of char...
the following sql statement does not update the field..
update contacts set do_not_synch = 'Y' where id=1
and if i open up the table in sql server management studio, and cannot type in and save the value Y...yes i can update other fields in this row...so its not a permissions issue?
I have update a table field and I like to know how do you "undo" the update? I set the field_length from 6 to 7 and like to undo this.Here is my command:
table = template_fileds column= field_length. filed name= field_name. update template_fields set field_length='7' where field_name='assesscode9'
i want to update date field but i am getting error like
update emp set convert(varchar(50),date_t,121) = '2006-03-31 19:56:36.933' Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'convert'.
Hi everybody, Could someone help me ??? Iam starting with sql
My problem ..
Ihave a table Table_customer
In this table a column Table_customer.no This column contain 8000 rows. with customer numbers: like 30789 I would like to modify once all this row adding just before the number a value to all these rows.This value will be a 60 So will have instaed of 30789 a 6030789 This column is an integer.
I have try a simple select wich give me this result How i can do this with update ? probably i have to convert fisrt to caracters
select kunr,nov_kunr= '30'+ltrim(str(kunr)) from event where kunr is not null
i have problem when i want to update another field in the same sql command i have tried this code: "SQL = "UPDATE items SET item_status= 'issued' WHERE NO_INVENTORI= '" & Text5.Text & "' " Set RS = Nothing Set RS = con.Execute(SQL) con.Close " an error occured said that" [Microsoft ](ODBC Microsoft Access Driver too few parameters.Expected 1." what that does mean? help me pliz..
