I want to update a field and in this field insert a increment count, for example:
When I make, "Select * from Users order by User" displays:
User1 | NULL
User1 | NULL
User1 | NULL
User2 | NULL
User2 | NULL
and I want to do this:
User1 | 1
User1 | 2
User1 | 3
User2 | 1
User2 | 2
how to do this?
I want to create a trigger that, when a field is updated or a record is inserted, counts the number of words in "field1", inserting this numeric value into another field in the same table. What would be the most efficient, fastest method for doing this?
View 15 Replies View RelatedHi is there away of doing both my select and update of a field in my database (m not using store procedures)
Basically all I need to do is get the value of DownloadTimes then add 1 and re update it.
Also to stop missing increments what the best way to lock the database while an update is in progress?
This is basically firing after a user clicks a file to download. I bring up the dialogue for them to save it is it possible to get which button they press and what is the best way to deliver it to them eg response.writeFile()??
Thanks sorry about all questions.
The reason why cust_id started at #4 and not #1 is because I failed to insert property three times in a row for having "Tatoine" instead of "WI" or a state less than 5chars nchar(5) correct? Then when I did a valid statement, the row was created at the starting number of four. I imagine this prevents users from having duplicate cust_ids. This however is also where rollback and similar commands could be handy correct or is there something more obvious I'm missing on a failed "insert into" to not increment the cust_id. The three rows 1,2 and 3 do not exist I believe and are not null. Having null values would of contradicted the table where two columns "not null" are a requirement.
CREATE TABLE customersnew
(
cust_idINTNOT NULL IDENTITY(1,1),
cust_nameNCHAR(50)NOT NULL,
cust_addressNCHAR(50)NULL ,
cust_cityNCHAR(50)NULL ,
cust_stateNCHAR(5)NULL ,
[code]...
HI
I have table with 20 columns. One of the column(col1) in the table has value like below
Col1 -----------Col2
123------------- 2
123--------------4
123--------------6
453--------------2
898--------------2
723--------------2
723--------------4
How to do insert or update increment of 2 in a column (col2) for every same value in the Col1?
I am using Access. Is it possible to with the query ?
I want to avoid iterating through a table row by row and programmatically incrementing a counter,/I have define a view called Q2 with the following fields of interest:
Trial varchar(25)
BI int
Track int
CAT int
What I would like to do is partition by Trial, BI, and Track, and count the number of times CAT changes value. In Access, I would just iterate through a recordset inside a function, but I would like to see if there is a set based solution.Adding Sample Data and Output.
Trial BI Track CAT
A 5 3 1
B 5 3 2
[code]...
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
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
updating the # of Payer from below query to match with the # of rows for each payer record. See the Current and desired results below. The query is currently counting the # of rows for all payers together and updating 3 as # of payers. I need it to count # of rows for each payer like shown inDesired result below. It should be showing 1 for first payer and 2 for 2nd & 3rd based on # of times each payer is repeated..
SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN,
(Select Count(*) From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c where a.CLAIM_ICN_NU =
c.CLAIM_ICN and b.TPL_TS = c.TPL_TS and a.CLAIM_TYPE_CD = 'X'
[Code] ....
Current Result
FILING_IND
PYR_CD
PAYER_ID
PAYER_NAME
CLAIM_ICN
#_OF_PAYER
[code]....
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View 6 Replies View RelatedConditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
View 13 Replies View RelatedI have the following querry :
SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
[Code] ....
It produced the following result
Based on my querry when n=2, it should be set to 0, why it cannot be set ?
if I added the value 2 to the list of values as below :
CROSS APPLY (Values(0),(1),(2)) d(n)
Then the value n=2 is updated but it adds extra duplicated rows which is not part of my real
I am using a BCP format file to import a CSV file. The file looks like the following:
"01","02"
The format file looks like the following:
6.0
2
1 SQLCHAR 0 0 """ 0 ""
2 SQLINT 0 0 "","" 1 MROS
3 SQLINT 0 0 ""
" 2 MROF
When both the two fields are set to SQLCHAR data types the data imports successfully without the quotes as 01 and 02. These fields will always be numbers and I want them as integers so I set the data type to int in the database and SQLINT in the format file. The results was that the 01 became 12592 and the 02 became 12848. where these numbers are coming from?
I have Table Staffsubjects with columns and Values
Guid AcademyId StaffId ClassId SegmentId SubjectId Status
1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1
I need to do 3 scenarios in this table.
1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this, But the problem is If do the update, It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier
[Code] .....
I have the following stored procedure, to insert or update a record and return the id field, however the procedure returns two results sets, one empty if it's a new record - is there a way to supress the empty results set?
ALTER PROCEDURE [dbo].[AddNode]
@Name VARCHAR(15),
@Thumbprint VARCHAR(40),
@new_identity [uniqueidentifier] = NULL OUTPUT
AS
BEGIN
UPDATE dbo.NODES
[Code] ....
Does anyone know if SQL has a built in function for returning increments of rows. For instance; I have a table with 100,000 rows, but would like to return 512 rows at a time. I've looked at the documentation and haven't came across anything that will do this. Any help is appreciated.
View 4 Replies View RelatedHow can I add an incremented counter to a select statement.
Such as select last_name,date,Count? from table, so I would
get
miles,1/1/2003,1
long,1/3/2003,2
smith,1/3/2003,3
First off, it has been a few years since I have done extensive work with SQL and that was using Oracle. But I am trying to develop a simple asset database for work, as we have nothing in place. I started out with Access, and decided to move to SQL express for many reasons.
What I have now is that I imported my data from my access 97 database to Excel, only my AssetTable did not import dates, I assume because Access and Sql Express handle dates differently... so a the time I just ignored that column.
Is it possible to insert the dates into the now populated SQL Express database AssetTbl where the AssetID's match? Here is what I have.
Sql Express Database Name: BAMS
Table Name: AssetTbl
fields: AssetID, SerialNum ...(many other fields)... DateAcq <- currently Null
Excel file: AssetDateAcq.xls
fields: AssetID, DateAcq (in format 07/12/2007)
To me it sounds like I need to do a short script/program to loop through the file read an AssetID from the excel file, and the DateAqcuired and then have it do an update on the DateAcq field, but it has been so long since I've done any work with SQL that I am finding there is a lot of "Dust" to blow off, and I don't know if I'm heading down the right track... or completely off course.
Thank you.
Hi,
I've got a table containing calculated values, so i created a field named "archived" (bit datatype) on this table, to prevent the values to be updated if this field is set to true.
Is it possible to created a constraint, to prevent the row to be updated if ARCHIVED=true ? How can i do it ?
I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005?
This is my existing code:alter PROCEDURE dbo.SP_FeaturedClassifieds
@PageIndex INT,
@NumRows INT,
@FeaturedClassifiedsCount INT OUTPUT
AS
BEGIN
select @FeaturedClassifiedsCount = (Select Count(*) From classifieds_Ads Where AdStatus=100 And Adlevel=50 )
Declare @startRowIndex INT;
Set @startRowIndex = (@PageIndex * @NumRows) + 1;
With FeaturedClassifieds as (Select ROW_NUMBER() OVER (Order By FeaturedDisplayedCount * (1-(Weight-1)/100) ASC) as
Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCountFrom
classifieds_Ads
WhereAdStatus=100 And AdLevel=50
)
SelectId, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount
From
FeaturedClassifieds
Where
Row between@startRowIndex And @startRowIndex+@NumRows-1
END
HiI have a field containing numbers. I want to do some simple arithmetics with it, say value=value+1 or value=value-1 or or even value+2. What is to be done, is fixed at design time. I think this could be done by loading the row or record to my program and doing the calculations there. And then storing the record back. But this seems too complicated.Is there a single query doing that in data table.
View 3 Replies View RelatedHi,
I have one Auto increment field in the table.
I have a problem that suppose I have 10 Records in the table, Now If someone delete record no 3 and 5 then it has 8 records.
But The field will give 11 no to new record I want it first fill 3 and 5 then give 11 to the new record.
Is it possible with the auto increment field in sql server 2005.
Hey,
Im building a site within Visual Studio 2005 and im using the SQL database system it provides. I currently have a number of tables each with a primary key that is set to the 'int' field type and also set to auto increment 1, 2, 3 etc etc. My question is it there a way to make this field increment automatically, providing a unique value but also automatically placing a couple of pre-defined letters before the number? Obviously it can't be an 'int' anymore because it'll be holding something like MEM1, MEM2 etc. Is there a way to do this is Visual Studio?
Many Thanks!
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
How I can get the following Desired SELECT STATEMENT with the increment of Max(serial_no)+1. Date will be in quotation in desired SELECT STATEMENT. All data is same. Just changing the serial_no which will be Max(serial_no)+1.
Create table #seq (serial_no numeric, Scode numeric, Sdate datetime, SErr char(10))
insert into #seq values (1,1002,'10/10/2015','SEDT')
insert into #seq values (2,1005,'12/10/2015','PEFT')
insert into #seq values (3,1004,'1/1/2015','QEGT')
insert into #seq values (4,1008,'1/2/2015','TEWT')
insert into #seq values (5,1007,'3/10/2015','REDT')
Result from the above query
serial_no Scode Sdate SErr
1 1002 10/10/2015 SEDT
2 1005 12/10/2015 PEFT
3 1004 1/1/2015 QEGT
4 1008 1/2/2015 TEWT
5 1007 3/10/2015 REDT
Desired SELECT STATEMENT Result with increment of serial_no (Max(serial_no+1)
Select 6, 1002, '10/10/2015', 'SEDT'
Select 7, 1005, '12/10/2015', 'PEFT'
Select 8, 1004, '1/1/2015', 'QEGT'
Select 9, 1008, '1/2/2015' ,'TEWT'
Select 10, 1007, '3/10/2015', 'REDT'
I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product.....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input.......I'm very sorry but I'm having a hard time putting this into words for some reason..umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?ThanksAdam.
View 4 Replies View Relateddeclare @kk int
set @kk=0
insert into tblSSAppsOrgEntityToEmployerMapDiffer
(Id,
OrgEntityCode,
EmployerId,
[Default],
[Code] ...
In above example Id is PK for Differ tbl and Temp tbl not having field related to this. thats why i have to take and increment that Id value manually.... but like above way i m getting error ..........
Hello..
can anyone help me with this query string?
String SQL = "INSERT Employee(Employee ID, UserName, JobRole, Department, Level, Email)(SELECT max(EmployeeID) + 1 FROM Employee) AS Employee ID, VALUES(EmployeeID, '" + newUserName + "', '" + newJobRole + "', '" + newDept + "', '" + newLevel + "', '" + newEmail + "')";
I am trying to insert values into a table, but i have an Employee ID field, which needs incrementing. How can i do this through my SQL query string? Is this possible? As it can't accept a NULL value.
Thanks, Sandy
Hi All,
I am trying to write a query that takes the max recordID on table A, and increment it by 1 for every record that is inserted into table A. The recordID field does not identity field property turned on.
Can you give me some help in getting this done? Is what I am trying to do even possible?
Thanks in Advance for your help.
I have a daily record table (has field Date1, field2Update) and have another table(has field Date2, Flag (value: 0 and non 0 values)
I want to update table1 and set field2Update as somevalue where table2.Flag <> 0
Greetings all,
I have a bit of brainteaser that's going to take some serious thought.
I'm importing information from .xls files into a SQL table. The problem is I need to check for dupes and increment certain fields on success of dupe find and then not insert or delete the dupes.
For example, if I have Adam, Turner, 32, 50 already in the table and someone tries to insert Adam, Turner, 32, 50...I need it to increment to read Adam, Turner, 64, 100 and not insert the record. (Notice 2 fields were incremented.)
With that, I have created an INSERT trigger as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[trgInsertCheck]
ON [dbo].[MyTable]
FOR INSERT
AS
BEGIN
EXEC sp_UpdateDupes
EXEC sp_DeleteDupes
END
The first stored procedure checks for dupes and updates if any dupes are found as follows:
--------------------------------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_UpdateDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int
DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets
FROM MyTable
GROUP BY FirstName, LastName, Age, Widgets
HAVING COUNT(*) > 1
OPEN c1
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE MyTable set Widgets = Widgets + @Widgets, Age = Age + @Age
WHERE FirstName = @FirstName AND LastName = @LastName
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
END
CLOSE c1
DEALLOCATE c1
Lastly, it finds all dupes, deletes them and inserts one row back in as follows:
--------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_DeleteDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int --declare all fields in table
DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets
FROM MyTable
GROUP BY FirstName, LastName, Age, Widgets
HAVING COUNT(*) > 1
OPEN c1
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
WHILE @@FETCH_STATUS = 0
BEGIN
--Delete all dupes...the cursor remembers the current record
DELETE FROM MyTable
WHERE FirstName IN (SELECT FirstName FROM MyTable GROUP BY FirstName HAVING COUNT(FirstName) > 1)
AND LastName IN (SELECT LastName FROM MyTable GROUP BY LastName HAVING COUNT(LastName) > 1)
AND Age IN (SELECT Age FROM MyTable GROUP BY Age HAVING COUNT(Age) > 1)
AND Widgets IN (SELECT Widgets FROM MyTable GROUP BY Widgets HAVING COUNT(Widgets) > 1)
--insert the current record back into the table
INSERT INTO MyTable(FirstName, LastName, Age, Widgets) VALUES(@FirstName, @LastName, @Age, @Widgets)
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
END
CLOSE c1
DEALLOCATE c1
Is there an easier way to do this?
(I know Age doesn't make much sense in this example but just replace it with a field would logically be incremented such as wadgets.)
Adamus
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
I have imported a whole bunch of tables. Most of them have an ID (int) column. Is there a way to set the ID columns across all tables to auto increment Primary Keys in bulk?
View 11 Replies View Related