If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;
Obviously... The stuff inside the IF is wrong syntax...I mean
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
I have a Contact table where I enter a "Parent" (Mother or Father) with IsSubscriber = 1. I also enter all of their children in this same table, with IsDependent = 1.
I then have a Relationship table that relates each child to the appropriate parent record in the Contact table.
I need to assign a sequence number to each child ONLY if they were a multiple birth (twins, triplets, etc.; all have the same DOB). I've been successful at writing a query using ROW_NUMBER(), but it includes the single births (no other child of the same parent has the same DOB).
Stripped down version of Tables and Data and my failed attempt to write a query to do what I want:
IF OBJECT_ID('TempDB..#Contact','U') IS NOT NULL
DROP TABLE #Contact
CREATE TABLE #Contact (
ContactId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, IsSubscriber BIT
[Code] ....
This is as close as I can seem to get.
hello friends
i want to update one column from my table regularly on sequence number i.e. 0,1,2,3,4,5
i created procedure but it is not working as per my output
declare @IndexIDGen int
declare @ID int
set @ID = 0
update temp_test set indexid = NULL
declare IndexIDGen cursor for select indexid from temp_test
open IndexIDGen
FETCH Next from IndexIDGen into @IndexIDGen
while @@fetch_status = 0
begin
update temp_test set indexid = @ID where indexid is null
set @ID = @ID + 1
print @id
fetch next from IndexIDGen into @IndexIDGen
end
close IndexIDGen
deallocate IndexIDGen
where i am going in wrong direction ??????
T.I.A
Create Table Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) )
insert into Sample
select 1, 1000, 1, 'Mike'
union
select 2, 1000, 2, 'Mikey'
union
select 3, 1000, 3, 'Michel'
union
select 4, 1001, 1, 'Carmel'
union
[code]....
select * from SampleI have here sample data given. What I want to do is, I want to check the RefID which is not having proper order of sequence number. If you see the RefID 1000, 1001 they are having properly sequence order in SeqNo field. But it is not in RefID 1002. RefID 1002 does not have proper order. It is because user has deleted a row which was having seqno 2. So i want to get what are all the RefID's are not having properly sequenced. So that I would be able to know these are all the RefID's are affected by delete statement that was done by user.
Hi, can anyone teach me how to automatic create a invoice number and insert or update it to a column?
View 2 Replies View RelatedI have two tables. One table has empty column (ID) and I want to generate serial numbers in that column based two tables columns(LoanNum) condition.
Table A
ID LoanNum
Null 1234
Null 2345
Null 3456
Null 4567
Null 5678
Null 6789
Table B
LoanNum
1234
2345
3456
4567
5678
6789
2324
4352
4235
Id is not primary key, but should not duplicates keys, it is just a column want to generate serial number (1,2,3,4...etc) How to generate?
Here is my problem. I have a table with 4 columns id1,id2,id3,boxnum. Here is some sample data.
id1 id2 id3 boxnum
1 1 1
1 1 1
1 2 1
1 2 1
1 2 1
2 2 2
2 3 4
What I need is to be able to update boxnum with sequential numbers based on the unique set of values from the id columns. So my output would be this.
id1 id2 id3 boxnum
1 1 1 1
1 1 1 2
1 2 1 1
1 2 1 2
1 2 1 3
2 2 2 1
2 3 4 1
I'm trying to create a column of numbers that increment by one.
I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).
I want to add an Id column to this query:
Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd
cross join INFORMATION_SCHEMA.COLUMNS ic
where sd.name = 'ODS1stage'
order by TABLE_SCHEMA,TABLE_NAME
How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.
I have the following data in a table:
Account SEQ
12345 1
12345 2
12345 4
12345 5
12345 7
I need to fix the SEQ field so that no gaps exist, like this:
Account SEQ
12345 1
12345 2
12345 3
12345 4
12345 5
Is there a way to do this with T-SQL?
Is is possible to Generate sequence numbers in a Query result in an SQL select Statement
like
Select sno,employee_name from empmaster
sno being a number with auto increments(this Field will not be in a Table).
I do not want to use Identity .....
This is Just to Provide a Serial Number to the Query Output...
thus the Out put should be something like
sno employeename
--- ------------------
1 abc
2 jhjk
3 kljl
Hi
I have a table with one column
Shop Name
---------------
123 Shop
#56 Shop
@Shop
Astar Shop
Best Shop
I want to write a sql select statement and produces the following result
Shop Name
---------------
123 Shop
#56 Shop
@Shop
Please help me on this
Cheers
-Jag
hi all
I am stuck with something that seems easy but im obviously clueless as how to execute the idea.
I have a custom table that houses invoices on the details level. So for example i have:
InvcNo
00000001
00000001
00000001
00000002
00000002
00000003
and so forth
What I am wanting to do in another column is keep track of the sequence number for each distinct invoice like:
SeqNo
1
2
3
1
2
1
I am working in a stored proc and i cant get past adding the numbers up at each line as a whole and not reseting when the next invoice number is present. Any help would be so greatly appreciated.
Thanks
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
I'm having problems with my website. I wish to make a section that has names starting with numbers only. For example, with every name beginning with "A", I have this code:
select * from names where name like "a%" order by name
and this works fine, but I want it so it will only list titles starting with 0-9..
Hi,
What transformations can be used to generate sequence numbers in a data flow?
I have a procedure which updates a sequence number in a table such as the one below.
Seq Sequence_Id
------ ------------------
NextNum 1
This is the procedure ...
create procedure DBO.MIG_SYS_NEXTVAL(@sequence varchar(10), @sequence_id int)
as
begin
update mig_sys_sequences
set
@sequence_id = sequence_id = sequence_id + 1
where
seq = 'CSN'
return(@sequence_id)
end
The purpose of this is to generate a sequential number each time the procedure is called. This number would then be used in a number of different tables to allocate a unique id so that the id is unique across the different tables.
1). What is the most efficient way of allocating these unique ids? The tables that I plan to update will already be populated with data.
2). How would I call the above procedure from an UPDATE statement?
Many thanks,
Fred
I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
my example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
I have a question in SQL server. For example I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
Example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[Code] ....
Hi,
I would like to know different possible ways in appending extra values like new uniqueidentifiers, sequence numbers, random number. Can you please tell what type of data flow components helps us ?
I have a database that has entries that I want sorted by date order. Each entry has an auto ID number allocated (primary key auto sequencing), which I want to change to reflect the sorting (so the first date has the first auto ID number and so on).I've gone into the database and sorted the entries as I want them. Then I've gone into Design View to delete and restablish the primary key autosequence. However, it is not keeping the date order in the database (ie entry ID 3140 date is 12/06/2015, but 3141 is 02/02/2012). How do I get it to maintain the order?
View 3 Replies View RelatedI have a student table like this studentid, schoolID, previousschoolid, gradelevel.
I would like to load this table every day from student system.
During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.
My question in my merge statement something like below
Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid
When matched then update
set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;
My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?
my table payment_details structure is
payment_id payment_code
1 null
2 null
3 null
4 null
here payment_id is a primary key and i need to update the whole payment_id column to payment_code column.so i just tried the below query
update payment_details
set payment_code = payment_no
where payment_code is null
but it shows subquery error?
I am looking for standard sql code for below 2 concern.
1- I want to drop the column Rowchecksum to all the table where table name start with ArchiveBbx
2-I want to update all the table where table name start with ArchiveBbx
example:-
Update table Archivebbxfbcc
set Rowchecksum=HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max)))
I am writing a query to update table variable. It is throwing me some error.
I have a table variable declared and inserted the data with three columns. I want to update col1 of that table variable when the second column of that table variable= one column from a physical table
update @MYtabvar set @Mytabvar.LatestDate=B.LatestDate from TableB B where @Mytabvar.id=B.ID
I would like to change a value after the table updates inserts a new row based on the value of that column
Table Name is: MPanel
Col Names: RID, FPID.
I want to:
If column RID='16'
Then UPDATE FPID='1'
How can I trigger that update? Would this work:???
CREATE TRIGGER dbo.MyTrigLI
ON dbo.MPanel
FOR INSERT, UPDATE
AS
BEGIN
UPDATE [MPanel]
SET
FPID='1'
WHERE (UPDATE(RID='16')
END
In a t-sql 2012 stored procedure, I would like to know how I can complete the task I am listing below:
In an existing t-sql 2012 stored procedure, there is a table called 'Atrn' that is truncated every night. The Table 'Atrn' has a column called 'ABS' that is populated with incorrect data.
The goal is to place the correct value into 'ABS' column that is located in the Atrn table while the t-sql 2012 stored procedure is excuting.
**Note: The goal is to fix the problem now since it is a production problem. The entire stored procedure that updates the 'dbo.Atrn' table will be rewritten in the near future.
My plan is to:
1. create a temp table called '#Atrnwork' that will contain the columns called,
Atrnworkid int, and ABSvalue with a double value.
2. The value in the column called Atrnworkid in the '#Atrnwork' table, will obtain its value from the key of the 'Atrn' called atrnid by doing a select into. At the same time, the value for ABSvalue will be obtained by running some sql when the select into occurs?
3. The main table called 'Atrn' will be changed with a update statement that looks something like:
Update Atrn
set ABS = ABSvalue
join Atrn.atrnid = #Atrnwork.Atrnworkid
In all can you tell me what a good solutiion is to solve this problem and/or display some sql on how to solve the problem listed above?
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.
Now, I was asked to log the specific record where the failure was occured.
Something like log the identity column value or primary key value which record has failed.
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052 Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
how to achieve this in single query?
In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.
So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:
UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))
However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.
how can I get the column names of a Temp table dynamically while doing an Update?
We have an application that runs Jobs, each of which affect ## number of child objects (usually around 1M). When a thread gets to 5000 updated child objects it bulk inserts into a table called ActionLog with the child Id and JobId.
When the job is complete a sproc SUMs the children from the ActionLog table:
select sum(id) from ACTIONLOG where JOBID = @JobId;
It then updates the Jobs table AffectedObjectCount column with the sum(*) from above.
Instead of writing to the ActionLog table and calculating the SUM at the end I would like to do this 'real time'. After the bulk insert I would like to update the AffectedObjectCount column with the number of rows that were just bulk inserted. I tried this in the past and ran into major contention issues. There are usually 20 threads running a job so there exists a lot of potential for deadlocks.
Is there a recommended way to handle updating one column on one row from multiple threads? What is the best practice for a counter like this?