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
ADVERTISEMENT
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
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
May 23, 2006
I am having a problem with an update statement. I am using compareallvalues with a SQL data source. When one of the old values is null the update does not go through.
i.e. This does not work if something is NULL
UPDATE myTable SET something = @somethingWHERE ID = @o_ID AND something = @o_something
Thank You,Jason
View 5 Replies
View Related
Dec 17, 2007
Hello - I have a column in a table (SQL 2005 EE) with a Data Type of smalldatetime and a Default Value of getdate(). When I insert a record from my webpage the new record contains the correct date via getdate(). However if I update the record from my webpage the date of the record then becomes NULL. Is this normal? Is there anything I can do about this from sql server? I am inserting/updating via an formview and ODS, using standard insert/update methods.
Cheers
Marco
View 4 Replies
View Related
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
Aug 19, 2002
I am trying to update a table that has a null value and keep getting a return of zero. I know that there are null values but cannot get the script to rcognize it. Example below
Use SimplexWFM
update per_Personnel_Info
set LAC5 = 'Default'
where LAC5 = '<NULL>'
Can anyone help?
View 2 Replies
View Related
May 6, 2008
Here's my table:
----------------------------
Members
----------------------------
Subscriber | Name
----------------------------
Subscriber is a True/False value. However, some members have a NULL value in the Subscriber field. I'd like to go through all the members and update the Subscriber field to be 'False' if the value in there is NULL.
View 3 Replies
View Related
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
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
Nov 7, 2007
Hi.I have a update query:
UPDATE test1
SET testprice= ((unitprice*8)/100) + unitprice
it's good.but I dont want to change the testprice if the unitprice is NULL.what should I do?
View 2 Replies
View Related
Apr 30, 2008
I am doing an update of MAINID column in a database table from excel. Now some of the MAINID data has string" NON CONVETBLE" and rest all are alpha Numeric. Now while i do the update these stiring " NON CONVERTBLE" is updated as NULL in the data ase table.
Now i dont want them to be upadated as NULL instead i want to put some Alpha Numeric string. such as "0000ABCD"
Please let me know how to do in this.
View 5 Replies
View Related
Oct 19, 2006
Hi, I am trying to use a formView with an update button to update individual records in an sql database. (when i click update it doesnt perform the update and just refreshes the page. ) One of the fields in my records is a NULL - this is also one of the fields that i need to update. When i manually go into the database and enter some data, and then go back to my form, it updates fine, but as soon as i delete the data from the field, it returns to NULL and im back to square one. Any Ideas on how to get around this problem?THanks
View 4 Replies
View Related
Nov 18, 2013
I have a grid with checkbox, where users can select multiple rows and edit at the same time and save it to the DB. Now I have used a Footer Template with textbox in the gridview. So if I want to put similar data's for some particular rows at the same time in the grid, I select the multiple rows and try to put values in the footer template textbox and when I click on save, it saves successfully.
UPDATE QUERY:
"UPDATE [Test] SET [Name]='" + Name + "',[Designation]= '" + Designation + "', [City]= '" + City + "' WHERE EmpID='" + EmpID + "'";
Now here is the challenge, but even when I enter null values in the footer template textbox it has to save with the old values of the rows and not null values. I tried it and couldn't make it happen. So anything like putting the case for each column and mentioning like if null accept the old value and not null accept new value.
View 5 Replies
View Related
Jun 23, 2015
i want to display the max Date in place of NULL when Indicator is "1"
INPUT
Emp Num
Date
Indicator
1
Null
1
[code]....
View 3 Replies
View Related
Oct 2, 2007
Hello everybody,
I can't perform an operation apparently very easy: set a field to a NULL value.
This is the db:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
This is the table:
CREATE TABLE [ProgettoTracce] (
[ID_Progetto] [int] NOT NULL ,
[MisDifDef] [real] NULL ,
[MisDifMeas] [real] NULL ,
[MisDifAna] [real] NULL ,
[MisDifID] [real] NULL ,
[MisDifCV] [real] NULL
) ON [PRIMARY]
GO
This is qry:
UPDATE ProgettoTracce
SET MisDifDef = NULL
WHERE ID_Progetto = 3444
The qry has been performed with no error. Then I execute SELECT * FROM ProgettoTracce WHERE ID_Progetto = 3444 and I find the value I tried to overwrite with NULL. If I update with 0 (for example) it works.
Obviously this happens on the production db, because on the development db the update with NULL works fine.
No transaction is called, db options are the same on dbs...
What's happen? Have I to call an exorcist???
Thanks in advance for any help!
Nicola
View 4 Replies
View Related
Jan 26, 2006
Hi guys, I have made several Access-based CMSs but now I am using SQL Server. I can read the records but my first attempts at writing are resulting in new records (with new ID) but all the fields are null.
I am posting the data from a form to the same page and an if /then statement catches the flag in the URL and runs the update script below. All the field names are correct.
if request.QueryString("add")<> "" then
Dim rsUpdateEntry
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
rsUpdateEntry.Open "SELECT * from generic_country_info" , oConn, 2, 3
rsUpdateEntry.AddNew
rsUpdateEntry.Fields("title1") = Request.Form("title1")
rsUpdateEntry.Fields("body1") = Request.Form("body1")
rsUpdateEntry.Fields("title2") = Request.Form("title2")
rsUpdateEntry.Fields("body2") = Request.Form("body2")
rsUpdateEntry.Fields("title3") = Request.Form("title3")
rsUpdateEntry.Fields("body3") = Request.Form("body3")
rsUpdateEntry.Fields("title4") = Request.Form("title4")
rsUpdateEntry.Fields("body4") = Request.Form("body4")
rsUpdateEntry.Fields("title5") = Request.Form("title5")
rsUpdateEntry.Fields("body5") = Request.Form("body5")
rsUpdateEntry.Fields("image1") = Request.Form("attach1")
rsUpdateEntry.Fields("image2") = Request.Form("attach2")
rsUpdateEntry.Fields("image3") = Request.Form("attach3")
rsUpdateEntry.Fields("image4") = Request.Form("attach4")
rsUpdateEntry.Fields("image5") = Request.Form("attach5")
rsUpdateEntry.Fields("country") = Request.Form("country")
rsUpdateEntry.Fields("dest_url") = Request.Form("dest_url")
rsUpdateEntry.Update
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
end if
Thanks
Mark
View 1 Replies
View Related
Oct 23, 2014
I am attempting to update a table that drives a report. The report is at the order level. An order can have several types of demand (revenue $): Ship and/or Backordered. When I update one of these demand fields on the report table, I would expect those orders that have that type of demand to update, and those orders that don't have that type of demand to remain at their current value ($0 in this example which is the table default). But what is happening is that orders that don't have that type of demand are changing to NULL. What am i doing wrong with my update statement that is causing this?
Code:
CREATE TABLE #sales(
OrdNo int,
StatusGrp varchar(10),
Demand decimal(10,2)
)
INSERT INTO #sales
VALUES (1,'Ship',100.00)
[Code] .....
View 6 Replies
View Related
Feb 6, 2007
Do I have to use condition split?, then union all?
if in script, I can use update from <tablename> Set column = isnull(column, 'NA'). It's so simple.
I'm also wondering can we run SQL Script against input dataset in a SSIS component?
View 6 Replies
View Related
May 16, 2006
Hi ,
I have 2 tables (Dept and Emp)
The columns in table Dept are Deptno and Deptname. Deptno is bigint and it is primary key. In Emp table, columns are Empno(PK) ,EmpName and Deptno(foreign key referring to Dept)
To Insert or Update record in Emp through application, value of Deptno is coming as 0(Zero). I want the value of Deptno to be inserted or updated as null if the value is Zero (0). How to do this in sql server 2005 by using trigger on table Emp
Thanks in advance
regards,
Srinivas Govada
View 6 Replies
View Related
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
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
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
Oct 16, 2006
Hi,I have an updatable DataGrid linked to a SQLDataSource in a web site developed using VS2005. Update works fine unless a value in the existing row is Null. Only one column in the database allows nulls.Putting a debug stop in the SqlDataSource1_Updating event, I checked the parameter value in the Immediate window and got the following result:?e.Command.Parameters(16){System.Data.SqlClient.SqlParameter}System.Data.SqlClient.SqlParameter: {System.Data.SqlClient.SqlParameter}DbType: Int32 {11}Direction: Input {1}IsNullable: FalseParameterName: "@original_FiresolveJobNo"Size: 0SourceColumn: ""SourceColumnNullMapping: FalseSourceVersion: Current {512}Value: NothingIs there a property I can set to generate a suitable Null check clause for the Update statement?Many Thanks,Keith.
View 3 Replies
View Related
Mar 9, 2015
I have a table with hundreds of millions of records and need to update an xml column to null. I do something like this:
UPDATE [Mydb].[MySchema].[MyTable]
SET [XMLColumn] = null
Currently it is taking around 6 hours.
Is there a quicker way to do this?
View 1 Replies
View Related
Sep 13, 2007
I have built a SSIS package that bascially updates two columns in table A...the update statement reads;
update Table A
set Colum 1 = ?,('?' is a variable)
Column 2 = ?
In my SSIS package, I would like to be notified/capture, if one or both variables are null....How do I do that ?..error log ?
The package runs fine both ways (if variables are null or not)
Thank you
View 1 Replies
View Related
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
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
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
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
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
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
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
