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


ADVERTISEMENT

Converting An Integer Field Into An Identity Field

Sep 14, 2006

I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?

TIA,



Barkingdog





View 1 Replies View Related

Help With Integer Field

Oct 7, 2004

hi all,

i have an autonumber field (primary key) and another integer field as part of a table. What i want to do is when a record is created, the default value of the integer field should be the_autonumber+1000 for eg record with pk 82 will have an integer field that's automatically 1082. Would it be possible to do this ? Thanks in advance.

View 2 Replies View Related

Bit Field Vs. Integer With Nulls

Aug 27, 2004

If I have a field where 99.9% of the time the answer is going to be "No", would I be better, in terms of disk space, using:

A bit field

OR

A tiny int field, with a NULL for the 99.9% that are "NO", and a 1 for those that are "YES".

I'm using SQL Server 7.0.

My application developer has no preference.

Thanks.

View 4 Replies View Related

Contains Keyword For Integer Field Type

Feb 17, 2007

I have a field ID of type integer, I want to put two numbers in that field: 3 and 7, so what I do is just store the number 37.Now, is there a command in SQL server which checks if the ID field contains a number I look for, say 7.Something like a CONTAINS keyword...If there is, could someone please tell me what it is AND tell me a bit more about it :memory usage, (dis)advantages etc.

View 1 Replies View Related

Setting Field To Integer From SqlDataAdapter

Apr 1, 2008

How do i get the result 'puzzle' from the SQL and assign it to Integer and use it to compare with Request.Form ? 
 Protected Sub Page_Load(ByVal sender As Object, _
                      ByVal e As System.EventArgs) Handles Me.Load
 
            Dim connString As String = _           ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString
 
           Dim strsql As String = _
           "Select CustomerID,CustomerNo,dbo.LA_DEC(Passkey) as passcode,dbo.LA_DEC(PuzzleKey) As puzzle, PuzzleFlag, NickName from Customers Where CustomerNo = '" & myAccount & "'"
            Using myConnection As New SqlConnection(connString)                  Dim myCommand As New SqlCommand(strsql, myConnection)
 
                  myConnection.Open()
                   Dim PuzzleDataSet As New DataSet
                  Dim PuzzleAdapter As New SqlDataAdapter(myCommand)                                    //                              <----    Coding here
 
 
 
                   myConnection.Close()
         End Using
 
End Sub

View 7 Replies View Related

Left Pad Zeros To Integer Field

Feb 4, 2004

I have an integer field that ranges from 3 to 6 numbers and I need to left pad leading zeros so the field is always a char(7). What is the syntax to do this? Thanks in advance.

0001234
0012345
0123456
1234567

View 1 Replies View Related

Scripting A Default Value For An INTEGER Field

Jan 28, 2008

Hello,

I am trying to write an SQL command to set the default value of an integer column I have within a table.

ALTER TABLE TableName alter column ColumnName int default 0

Any suggetions?

View 3 Replies View Related

Getting An Integer Range Out Of A String Field

Oct 9, 2007



We have account numbers that are string values, looking like: "01.02.02.00.0040.000.000".

We need to parse individual segments and pull out a range of values treating the segment like an integer. For example we would like all accounts that have the fifth segment ranging in values from .0040. through .0060. inclusive.

We've been trying to do something with the like clause: LIKE '__.__.__.__.[0-0][0-0][4-6][0-0]'.

We want 40 through 60 but we're getting 40, 50, and 60. If we change the last bracket to [0-9] we get 40 through 69.

Does anyone have any suggestions as to either fix our like clause or another approach?

Thanks.

John

View 4 Replies View Related

Char(4) Or Integer For Year / Quarter Field

Sep 3, 2007

Hello,

what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?

Both are part of a composite index.

Thanks
Silas

View 6 Replies View Related

How To Re Number Integer Field Values In A Table

Sep 22, 2014

I am facing problem with re numbering of field records in a table.I am having one table with below records.

sid(identity/primarykey) stickyId(Integer) UserId(integer)
102 0 171
103 1 171
104 2 171
105 3 171

here how to renumbering stickyId values when deleted particular stickyId from UI. Here stickyId field is Integer type only. not primarykey/identity field.

View 3 Replies View Related

How To Concatenate Two Fields In A Textbox One Integer And Other Charecter Field

Jul 2, 2007

I have the folliwing two fields, want tp concatenate:



=Fields!sequenceno.Value & =Fields!LogType.Value



Thank you very much for the information.

View 3 Replies View Related

In Stored Proc - How Do I Find The Next Key Value (integer) And Use It To Populate A Field In The New Record.

Nov 27, 2006

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) 

View 1 Replies View Related

Update A Integer Data Type

Jul 15, 2007

i have problem updating the integer in the access table i have the the statement as below but it appear to have an error data mismatch,any1 can help me please
Dim updatethread As String
updatethread = "UPDATE Thread " + "SET replies = ' replies+ 6'" + "where threadid=@threadid"

View 7 Replies View Related

DB Design :: Convert Integer Date (MMDDYY) And Integer Time (HHMMSS) To DateTime Format?

Jul 20, 2015

Working on a new database where the Date and Time are stored in a Date Time Field.

Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:

transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)

How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?

This works well for converting the transDate Part in the select statement:

   dbo.IntegerToDate(at.transDate) as transDate

   * That returns: "2015-07-16 00:00:00.000"

* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!

Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.

View 3 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

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







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