Transact SQL :: How To Update ParentID And RowOrder

Aug 24, 2015

I have this question is the extension of my previous question.I have a table,

DECLARE @TBL TABLE (RowNum INT, DataId int, RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 1.00, NULL),
(2, 105717, NULL, NULL),
(3, 105718, NULL, NULL),
(4, 105509, 2.00, NULL),
(5, 105510, 3.00, NULL),
(6, 105514, NULL, NULL),
(7, 105513, 4.00, NULL),
(8, 105719, NULL, NULL),
(9, 105718, NULL, NULL),
(10, 105718, NULL, NULL)
SELECT * FROM @TBL
 
Initially I asked question related to update "RowOrder", now I need to update "ParentId" also. example : if master has RowOrder 1.00 and there are 2 child's before 2.00 (another master) , then child entites RowOrder will be 1.01 & 1.02 & ParentId for both should be = 105508 and so on...

I got below query to update "RowOrder" from another thread,;

with tmp(Original_RowNum, Original_DataId, Original_RowOrder,New_RowOrder) as
(
select RowNum, DataId, RowOrder,cast(RowOrder as DECIMAL(18,2)) from @TBL where ROWNum = 1
union all
select t.RowNum, t.DataId, t.RowOrder,case when t.RowOrder is null then cast(tmp.New_RowOrder + 0.01 as DECIMAL(18,2)) else cast(t.RowOrder as DECIMAL(18,2)) end from @TBL t inner join tmp on tmp.Original_RowNum + 1 = t.RowNum
)
select * from tmp

how to update "ParentId" for table @TBL.

View 2 Replies


ADVERTISEMENT

Transact SQL :: How To Update RowOrder

Aug 24, 2015

I have a below table, I have RowOrder for master entity and need to update child entity,example : if master has RowOrder 1.00 and there are 2 child before 2.00 , then child entites RowOrder will be 1.01 & 1.02 and so on...

DECLARE @TBL TABLE (RowNum INT, DataId int, RowOrder DECIMAL(18,2) NULL)
INSERT INTO @TBL VALUES
(1, 105508, 1.00),
(2, 105717, NULL),
(3, 105718, NULL),
(4, 105509, 2.00),
(5, 105510, 3.00),
(6, 105514, NULL),
(7, 105513, 4.00),
(8, 105719, NULL),
(9, 105718, NULL),
(10,105718, NULL)
SELECT * FROM @TBL

I need to update RowOrder and looking for below output,

View 9 Replies View Related

Transact SQL :: Types Don't Match Between Anchor And Recursive Part In Column ParentID Of Recursive Query

Aug 25, 2015

Msg 240, Level 16, State 1, Line 14

Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,

DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),

[code]....

View 2 Replies View Related

ParentID Vs. ChildID

Jul 11, 2007

I consulted many books, articles, posts on trees and hierarchies realized in SQL (databases)
And all of them use ParentID for linking but not ChildID.

Why is it?
What are possible pitfall, catch33 in basing the trees/hierarchies on linking through ChildID instead of ParentID?

View 16 Replies View Related

Transact SQL :: Firing After Update Trigger - On Table Row Update

Jul 8, 2015

I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
 
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),

I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.

I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?

View 7 Replies View Related

Transact SQL :: Update Next Row With Above Value

Aug 25, 2015

Updating the Table . I need to update Two Column .. DEPT and Product .

For a Same EmployeeName DEPT and Product Name should Repeat till Next Value in encountered.

Example For EmployeeName 'A' Dept Value 100 should Repeat till 20 comes, based on Date, because 100 comes before 20 and Project P1 should repeat till P2 comes.

Below is the Sample Source Table.

CREATE TABLE #MYTEMP3 (NAME VARCHAR(50), DEPT INT ,PROJECT VARCHAr(100), ORDERDT DATE)
INSERT INTO #MYTEMP3
SELECT 'A',100,'P1', '2015-01-01' UNION ALL
SELECT 'A','','', '2015-01-02' UNION ALL
SELECT 'A','','', '2015-01-03' UNION ALL

[Code] ....

Looking for UPDATE Query . I do not want to use Common table expression to achieve this .

View 4 Replies View Related

Transact SQL :: After Update Trigger

Aug 27, 2015

I would like to make an after update trigger on a table (ARD TABLE) where depending on the criteria the trigger will either insert, update, or delete a row in the trigger table. Basically, in the trigger table there can only be rows where the column status is > 0. So, if the ARD TABLE has a row updated that used to have a status of 0 and now the status changed to 1 then insert it into the trigger table. If the ARD TABLE had a row that used to be 1 and now it's 0 then delete it from the trigger table. And if the ARD TABLE had a row that was 1 and now made any change to that row we need to update the corresponding row in the trigger table.

View 4 Replies View Related

Transact SQL :: Run Update With Intersect

Oct 7, 2015

I am trying to update all records in #newtable that exist in #mastertable.  I have been using Intersect to show me the duplicate records, but now I need to update a field in #newtable   This was my syntax to show the records that exist in both tables, how can I change this to an update statement?  The field in #newtable I want to update is [alreadyexists] and I want to update it with a yes value  

So update #newtable set [alreadyexists] = 'yes'
select uno, mucha, pablo, company from #Newtable
intersect
select uno, mucha, pablo, company from #mastertable

View 5 Replies View Related

Transact SQL :: How To Update A Column

May 19, 2015

I have a table that has two columns. One column has ID from 1 to 1800. The other column is null. I want to update the second column with values from 29 to 43. So for ID 1, value will be 29, ID value will 30 and it goes to 43. Then after it will start from 29 again and goes to 43. It goes all the way to the highest ID, i.e. 1800.I have added a script for better clarity 

declare @t table (stuID int, valueID int)
insert into @t (stuID, valueID)
values (1,null), (2,null), (3,null), (4,null)

the null part is what I want to have from 29 to 43 .... all the way untill the ID reachs to 1800

View 5 Replies View Related

Transact SQL :: UPDATE With OUTPUT Clause

May 28, 2015

I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below

UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL

The above code is throwing an error as follows:

Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113

The multi-part identifier "s.TCK_NR" could not be bound.

View 4 Replies View Related

Transact SQL :: Update Table From Another With Conditions

Dec 2, 2015

I am trying to do a simple update in one sql table from another where a certain condition in both tables are met.I am missing something basic and have tried numerous posted examples, without success.Here is my code (that does not update) :

        opdragaliasnaaminsit.Connection = konneksie
        opdragaliasnaaminsit.CommandText = "UPDATE vyfjaarontledings " & _
        "SET aliasnaam = T2.aliasnaam" & _
        " FROM  vyfjaarontledings T1" & _
        " INNER " & _
        "JOIN blokke T2 " & _
        " ON T1.plaasno = T2.plaasno " & _
         "WHERE T1.plaasno = T2.plaasno"
opdragaliasnaaminsit.ExecuteNonQuery()

I am trying to update aliasnaam in vyfjaarontledings from blokke.

View 10 Replies View Related

Transact SQL :: Update Statement With Cursor

Jun 16, 2015

When I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?

Declare
@count int,
@Assignment varchar(100),
@fullname varchar(100),
@timeworkedtoday decimal(18,2),
@badgeNum varchar(50),
@ticket varchar(50)

[Code] ....

View 5 Replies View Related

Transact SQL :: Transpose Data Update

Jul 15, 2015

I have following sample data

I want this data updated to different table based on PK value relationship like below,

What shall I do to make such update ? Here is the sample script I want ot use

 DECLARE @Activity TABLE(PK BIGINT,Value1 NVARCHAR(10),Value2 NVARCHAR(10))
 INSERT INTO @Activity(PK) VALUES(1)
 INSERT INTO @Activity(PK) VALUES(2)
 INSERT INTO @Activity(PK) VALUES(3)

[Code] .....

View 11 Replies View Related

Transact SQL :: Update Null Values

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

Transact SQL :: How To UPDATE More Than 500 Records In One Call

Aug 13, 2015

I have following query which is created dynamically as -

UPDATE  BUILDTABLE  SET BUILD_ID =  '984137'  WHERE SET_NUMBER = '1889147436' AND SEND_DATE = '1941-03-04'; 
UPDATE  BUILDTABLE  SET BUILD_ID =  '984137'  WHERE SET_NUMBER = '1115509374' AND SEND_DATE = '1991-09-01'; 
UPDATE  BUILDTABLE  SET BUILD_ID =  '984137'  WHERE SET_NUMBER = '1515579671' AND SEND_DATE = '1941-05-24'; 
UPDATE  BUILDTABLE  SET BUILD_ID =  '984137'  WHERE SET_NUMBER = '1795509670' AND SEND_DATE = '1958-01-14';
UPDATE  BUILDTABLE  SET BUILD_ID =  '984137'  WHERE SET_NUMBER = '1915508672' AND SEND_DATE = '1961-09-07';

Here till " UPDATE BUILDTABLE SET BUILD_ID =  '984137'" is the same clause for all queries, but "where" condition is different for all queries. I have to update more than 500 UPDATE statements(like above) in one call. Currently I am concatenating all the queries in string Builder which is time consuming.I want to increase performance of application.Any other class like BulkCopy ?

View 18 Replies View Related

Transact SQL :: Script Update For Each Record

May 21, 2015

I have a csv file that contains numbers like

OtherName, AddNO
name1,NewV001
name2,NewV002
name3,NewV003

and there is sql table which contains below values
ID, AddN0
1,  OldV001
2,  OldV002
3,  OldV003

I need a sql query that reads New AddNo v001..V003 and update in Sql table for given range ID 1-3
OUtput
ID, AddN0
1,  NewV001
2,  NewV002
3,  NewV003

View 2 Replies View Related

Transact SQL :: Update Statement Run Error

Oct 1, 2015

I keep getting an error of error in statement near = on this update statement.  What is incorrect?

Update abcd
Set CAST(field1 As varchar(4000)) = 'Computers and 2-in-1s'
where CAST(field1 As varchar(4000)) = 'Computeres and and 2-in-1s'

View 11 Replies View Related

Transact SQL :: How To Update Table Without Using While Loop

Nov 15, 2015

I have two tables i have to update table2 using table1 without using while loop.

example given below.

Table1 

rid
id
amt
firdate
lastdate

1
1
500

[code]....

View 7 Replies View Related

Transact SQL :: Table Doesn't Get Update Through Job

Sep 28, 2015

I have following T-SQL statement:- 

insert into [dbo].[tbl_FG_Alert_Count_All_Report] ([Date] ,[Count] ,[Rule Type])
   SELECT TOP 10 [Date]
      ,[Count]
      ,[Rule Type]
  FROM [dbo].[tbl_FG_Alert_Count_All] where Count <>'0' and DATEDIFF(dy,date,GETDATE())  = 1 order by Date desc

When I ran this T-SQL statement in SSMS; I don't get any error and as expected, I can see new data in [dbo].

[tbl_FG_Alert_Count_All_Report] table. 

Now I created one job with same T-SQL Statement. Job completes successfully with out giving any error message; But unfortunately I don't see any new data in [dbo].[tbl_FG_Alert_Count_All_Report] table.  What would be the reason that I don't see new data when job completes successfully but I can see new data after executing same T-SQL statement in SSMS?

View 3 Replies View Related

Transact SQL :: Update Statement With A Greater Than Value

Apr 21, 2015

I created  this unique  codes and

I need all [FRMDAT] field set to ‘12/31/2014’ in the MKLOPT table, where the [JOBCOD]  in the VALUE list BELOW  have a  [FRMDAT] that is currently (greater than) > ‘12/31/2014’

VALUE LIST
PH00059 
PH02775      
PH03051      
PH03305      
PH03336      
PH03342      
PH03371      
PH03992      
PH03993      
PH03994      

View 2 Replies View Related

Transact SQL :: Update Same Row Multiple Times

Nov 9, 2015

I am trying to update the same row of the table multiple times. in other words, i am trying to replace the two stings on same row with two different values.

Example: if the column has a string "b" then replace with "B" and  if the column has a string "d" then replace with "D" . I can  write multiple updates to update it but i was just wondering if it can be done with single UPDATE statement

column before the update : bcdxyz
after the update: BcDxyz

Following is the sample data

declare @t1 table
(
    c1 varchar(5),
    c2 varchar(10),
    c3 varchar(5)
)
insert into @t1 values ('a','bcdxyz','efg')

[Code] ....

View 5 Replies View Related

Transact SQL :: Update With Most Recent Entry

Jul 24, 2015

We have 2 Tables, tblMain, which contains unique products and tblHistory, which contains a history of the product Records are added to tblHistory daily and then the prices in tblMain are updated with those prices. The problem that we have is that since there are multiple instances of the same product in tblHistory, we're not always updating using the most recent price. Below is the current SQL that we are using.

Update TM SET TM.Price = TH.PriceHist,
TM.DateUpdated = TH.DateUpdatedHist
FROM tblHistory TH LEFT JOIN tblMain TM ON TH.ProdCode = TM.ProdCode

Below is a sample of data in tblHistory and the records that I would need to be used for the update.

ProdIDPriceHistDateUpdateHist
115.257/20/2015
117.157/15/2015
120.357/23/2015
520.107/25/2015

With the above. I would need corresponding records in tblMain to be updated with the entry on 7/23/2015 for ProdID 1 and with ProdID 5

View 15 Replies View Related

Transact SQL :: Openquery Update With A Parameter?

Sep 17, 2015

I need to update an Oracle table from SQL Server. I am trying to use Openquery Update statement. I need to pass a integer as a parameter. I will be updating a date field and a status field.

This is the gist of what I need to do in a stored procedure

DECLARE    @ID1        INT,
        @SQL1        VARCHAR(8000),
        @STATUS     VARCHAR(10),
        @DATE        DATETIME;
SET        @ID1 = 350719;
SET        @STATUS = 'COMPLETED';
SET        @DATE = GETDATE();
SELECT    @ID1;
SELECT  @SQL1 = 'UPDATE OPENQUERY(NGDEV2_LINK2, ''select DM_IMPORT_STATUS, DM_IMPORT_DATE FROM NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID =  ' + CAST(@ID1 as nvarchar(30)) + ''')'
SET DM_IMPORT__STATUS = @STATUS, DM_IMPORT_DATE = @DATE;
EXEC (@SQL1);

View 5 Replies View Related

Transact SQL :: Update All Records In Table?

Oct 22, 2015

Every day the timestamp is changed on all rows in one of the table(table has 1 mio records).

What is the best way to find out which query is doing this?

Could be also query which updates only one record(or couple of them) but is executed for each record once.

I have been looking sys.dm_exec_query_stats and sys.dm_exec_sql_text for past but couldn't find.

I can't use event notifications since there is service broker disabled.

View 5 Replies View Related

Transact SQL :: Update Query Of Two Columns

Oct 23, 2015

I want to update the NewVol1 and NewVol2 in table #Rec2 from NewVol1 and NewVol2 of table #Rec1. It is some tricky because the value is NULL in some records of NewVol1 and NewVol2.

Update query. SAP, Code, Code1, Code2 and SAPdate are keyfield when join both table to update the NewVol1 and NewVol2 in table #Rec2 from NewVol1 and NewVol2 of table #Rec1.

Create table #Rec1 (SAP char(10), Code char(6), Code1 char(6), Code2 char(6), SAPDate datetime, NewVol1 char(20), NewVol2 char(20))
insert into #Rec1 values  ('SATRIP','AAA','AA','A','2014-01-01','838.88484884',NULL)
insert into #Rec1 values ('SATRIP','AAA','BB','B','2014-01-01','839.88484884',NULL)
insert into #Rec1 values ('SATRIP','AAA','CC','C','2014-01-01','848.88484884',NULL)
insert into #Rec1 values ('SATRIP','BBB','AA','A','2014-01-01',NULL,'383.48377373')
insert into #Rec1 values ('SATRIP','BBB','BB','B','2014-01-01',NULL,'385.48377333')
insert into #Rec1 values ('SATRIP','BBB','CC','C','2014-01-01',NULL,'675.48377234')

[code]....

View 4 Replies View Related

Transact SQL :: Update Statement With Case

Nov 11, 2015

I am trying to run the below but I get an error of 'Incorrect syntax ')''  --- I have tried every angle I can think of around the parens to fix this but nothing I do is working.

UPDATE abcdefg
SET [Date] = GETDate(),
[readytogo] =
(
CASE WHEN [customername] NOT IN (Select [customername] from [server].[database].[dbo].[view])
THEN 'Yes'
ELSE
'Needs Verification'

[code]....

View 5 Replies View Related

Transact SQL :: Update Element Name For XML Column

Jun 2, 2015

I have sample XML data which is shown below in my table. I need to update one of the column names in this field.

Changing <Desc> to <Description>

Input:

DECLARE @myxml
XML
SET @myxml
= '<Cust id="1">           
<Name>aaaaaaaaaa</Name>
             
<Desc>bbbbbbbbbb</Desc>
</Cust>'

Output:

DECLARE @myxml
XML
SET @myxml
= '<Cust id="1">
             
<Name>aaaaaaaaaa</Name>
<Description>bbbbbbbbbb</Desc>
</Cust>'

View 4 Replies View Related

Transact SQL :: How To Update Table In Many To Many Relationship

Nov 30, 2015

I am having challenge to update the redemption table from the multiple card activation table.  I want to update the redemption table with the activation date closest to the redeem date.  

For example:  Redeem date 20071223, I need to update the top row Date, Year, Period fields from the Card activation table.

Redeem date 20071228, I want to refer to the second row in the Card activation table date 20071228.  
Redeem date 20080316 or later, I want to use the last row in the card activation table date 20080316.

How to modify the update query to select the right activation row accordingly?

Below is my partial code I used but it always pick the 20071223 date to update my redemption table.

 CREATE TABLE #Card
 (
       [CardNumber] varchar(20)
 ,[ Date] int
 ,[ Year] int
 ,[ Month] int
 ,[ Period] int
  )  
 
[Code] ....

View 13 Replies View Related

Transact SQL :: Update Column With Row Number

Jul 23, 2015

I have a Users Table. It is full of users already and I would like to start using the UserPIN in the software (this is an nvarchar column).

I would like to update the UserPIN column with the row_number. All of my efforts have resulted in setting the UserPIN to 1 for every record.  I just want an update query that fill the UserPIN column in sequential order.

View 6 Replies View Related

Transact SQL :: Update Statement In Merge Does Not Work

Jul 29, 2015

In the following t-sql 2012 merge statement, the insert statement works but the update statement does not work. I know that is true since I looked at the results of the update statement:

Merge TST.dbo.LockCombination AS LKC1
USING
(select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type

[Code] ...

Thus can you show me some t-sql 2012 that I can use to make update statement work in the merge function?

View 3 Replies View Related

Transact SQL :: Column Update With Comparison With Another Table

Nov 10, 2015

I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.

Txt_Temp - Table

I tried this code with no succes and only one row is updating

update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber

OR Add this code in view 

Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code 
from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber

Vin
123456
123123
123789

Resrve_Temp1 - Table
asddfghjklk123654
asddfghjklk123456
asddfghjklk321564
asddfghjklk123123
asddfghjklk123789
asddfghjklk654655
asddfghjklk456465

My Result can be in Txt_Temp table or new table or with one or two columns

asddfghjklk123456 123456
asddfghjklk123123 123123
asddfghjklk123789 123789

View 10 Replies View Related

Transact SQL :: Update Table Based On Join

Sep 7, 2015

I have table A with colums ID and Product. I have table B with ID (same as table A) and revenue of product.

I need to update the field Product of table A with the revenue of table B.

I'm confuse with the update joining both tables. I tried this, but obviously has an error:

Update A set Product=B.Revenue where A.ID=B.ID

View 6 Replies View Related

Transact SQL :: How To Update Multiple Records In A Table

Jul 23, 2015

I need to update more than one record at once. I have ~ 100 records that I have to update and don't want to execute query 100 times.

My query looks like this:

Update Table1
Set Table1.field1 = ( select Table2.field1 from Table2 where Table2.field2 IN ('a', 'b', 'c')
where Table1.field2 IN ( 'a', 'b', 'c')

It obviously failed because subquery returned more than one value and error message stated that I can't use '=' operator in this case.

My question: how could I update the same column from many records in one execution?

View 10 Replies View Related







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