Update A Computed Field

Dec 28, 2003

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

View 6 Replies


ADVERTISEMENT

Computed Field References

Feb 18, 2004

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.

View 9 Replies View Related

Bind Computed Textbox Value To Database Field

Sep 11, 2007

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)
 
 
thanks for any help

View 3 Replies View Related

Does Computed Column Update On Select General Question

Sep 22, 2006

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? 

View 1 Replies View Related

Update Field With Trigger Only If A Specific Field Is Updated

Nov 11, 2013

I want to update a field with a trigger only if a specific field is updated.

When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?

ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

[Code] .....

View 4 Replies View Related

SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE

Apr 16, 2015

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

View 9 Replies View Related

Fail To Update Field With A Field Uniqueidentifier

Mar 30, 2004

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.

View 2 Replies View Related

Update SQL Field With Stripped Data From Other Field

May 12, 2006

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

View 1 Replies View Related

How To Update Another Field From Combo Field

Oct 31, 2012

In Access, you have a combo with column designations for example me.combofield.column(x) and you can update another field with those column(x) values.

How do you do it in MS SQL?

I didn't mean "from a 'combo' field'" in SQL(!) I just want to reproduce the equivalent of an Access combo box.

View 14 Replies View Related

How Do I Update A Field Only If Update Value Is Not Null?

Oct 25, 2006

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?

What I mean is something like:

---------------------------------------------------

Update database.dbo.table set

if(@newvalue !=null)

     afield = @newvalue;

-------------------------------------------------

 

Really need your help on this. Thanks.

View 9 Replies View Related

Computed Column Referencing A Computed Column

Sep 5, 2007

Hi all,

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.

Could anyone offer some advise on my situation.

Any input apreciated.

View 1 Replies View Related

Update A Field

Jan 4, 2006

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.



Thank you very much.

View 3 Replies View Related

Trying To Update In A Field That Contains Xml

Nov 11, 2013

Trying to run this SQL script

update Promotions

set PromotionDiscountData = '<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-11T00:00:00</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-12T00:00:00</ExpirationDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule">

[code]...

but getting this error

Msg 402, Level 16, State 1, Line 1

The data types xml and varchar are incompatible in the equal to operator.Basically within each cell I am trying to change the StartDate only

View 5 Replies View Related

Update Field Without Retreive

Jan 24, 2007

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
 

View 1 Replies View Related

Set Update Field Automatically

Feb 11, 2007

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.

View 2 Replies View Related

Update A Field With Count (*)

Jan 3, 2005

Hi,

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?

Many thanks!!

Eric

View 1 Replies View Related

Update On Joined Field

Jun 6, 2006

Is it possible to update the filed used in the inner join
 
Update t1 set t1.name=t2.name2
From t1 inner join t2 on t1.name = t2.name
 

View 1 Replies View Related

Update A Decimal Field To 0

Jun 12, 2001

Hello,

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 -

Update Table1
Set [Decimal Field1] = 0,
[Decimal Field2] = 0,
[Decimal Field3] = 0

is taking forever (1+ hours) to run.

Either I may be doing something stupid here or I am missing
something vital.

Thanks for any help.

View 2 Replies View Related

To Update A Field At Run-time

Jun 19, 2001

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)

Thanks

View 3 Replies View Related

How To Update Text Field

Sep 8, 2004

Hi all,

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 @case VARCHAR(20);
DECLARE @note VARCHAR(80);
DECLARE @lineno VARCHAR(5);

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;

View 3 Replies View Related

Update A Field Using A W/Join. Please Help.

Nov 21, 2005

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

View 2 Replies View Related

Update Field In Two Tables

Jul 25, 2006

This is my curren code for updaing ESN number. But this is incorrect.

<asp:SqlDataSource ID="ESNTrackingDataSource"
UpdateCommand="UPDATE [ESNTracking] SET [EsnNumber] = @EsnNumber WHERE [EsnTrackingId] = @EsnTrackingId"
OnInit="ESNTrackingDataSource_Init"
OnUpdating="ESNTrackingDataSource_Updating"
OnUpdated="ESNTrackingDataSource_Updated"
runat="server">
<UpdateParameters>
<asp:Parameter Type="String" Name="EsnNumber"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>

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

View 1 Replies View Related

NText Update Field

Aug 17, 2004

Hi All,

I will be doing stress test for my app.

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

View 1 Replies View Related

Update A Varchar Field

Oct 4, 2004

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.

View 4 Replies View Related

Update A Varchar Field

Oct 4, 2004

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.

View 1 Replies View Related

Update Char Field...

Jul 12, 2007

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?

is there anything special i should have to do?

Cheers,
Justin

View 1 Replies View Related

How To Update 2 Records On 1 Field

Aug 20, 2013

I have this code

I want to update nilai = total where kd_bulan = 9 and nilai = 0 where kd_bulan = 12

View 13 Replies View Related

How To Undo And Update To Field

Jan 17, 2014

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'

View 7 Replies View Related

ASP UPDATE SET FIELD = NULL

Sep 4, 2003

Using an SQL server and writing ASP code trying to set a numeric field to null.
ie ssql = "UPDATE users SET customer_id = null where name = 'Joe'"

Keeps on generating a syntax error

View 2 Replies View Related

Update Datetime Field??

Apr 6, 2006

hello friend !!

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

please help me out

T.I.A

Shashank

View 20 Replies View Related

Update On A Integer Field

May 29, 2006

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

Thank

View 3 Replies View Related

Update ID Field Autocrement!help Me

Feb 21, 2007

I have a table Class include field ID,Name,Email. With ID field.All data not Autocrement ! I want Update ID field autocrement . Please help me

View 2 Replies View Related

How To Update Another Field In The Same Sql Comman

Apr 4, 2007

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

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved