November 14, 2017 at 12:01 pm
I want to change the old store to new store by using the query in MSSQL. For example,
Staff_Id Transaction_Date Staff_Name Old_Store New_Store
---------- --------------------------- -------------- ---------------- ------------------
1 1.1.2017 AA 1
1 2.1.2017 AA 2
1 3.1.2017 AA 1
1 4.1.2017 AA 4
1 5.1.2017 AA 3
I want to change the table to following like that
Staff_Id Transaction_Date Staff_Name Old_Store New_Store
---------- --------------------------- -------------- ---------------- ------------------
1 1.1.2017 AA 1 NULL
1 2.1.2017 AA 2 1
1 3.1.2017 AA 1 2
1 4.1.2017 AA 4 1
1 5.1.2017 AA 3 4
I wrote the query like that, but it's not true, it's output randomly
Update EC1 SET EC1.Old_Store=EC.New_Store
From Employee_Change EC , Employee_Change EC1
WHERE EC.Staff_Id=EC1.Staff_Id
AND EC.Transaction_Date>=EC1.Transaction_Date
November 14, 2017 at 2:18 pm
SQL doesn't by default have an "order" to return your result set by. So when you issue a command like "SELECT Transaction_Date FROM Employee_Change", depending on various factors, you could get it in the order you expect, or it could be all jumbled up. Same thing applies when you do an update like that.
What I would do first is change your update into a select. This will give you an idea of what it is actually doing and I expect it is not what you think it is doing. When I did that, instead of getting 5 rows, I got 15.
So your logic is wrong with the join. This is easy to see as when the date is 1.1.2017, there are 4 rows which are greater than it so that part of the join will give you 4 rows.
So what I think you will want to do is something like this:WITH cte AS
(
SELECT [Staff_ID] ,
[Transaction_date] ,
[Staff_name] ,
[old_store] ,
[new_store],
ROW_NUMBER() OVER (partition BY Staff_id ORDER BY Transaction_Date) AS [rn]
FROM Employee_Change
)
UPDATE [cte1]
SET [cte1].[New_Store] = [cte2].[Old_Store]
FROM cte cte1
FULL OUTER JOIN cte cte2 ON [cte1].[rn] = [cte2].[rn] + 1
I'd recommend doing a "begin transaction" and "rollback transaction" before doing the update though with selects inbetween so you can verify the data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 14, 2017 at 6:35 pm
bmg002 - Tuesday, November 14, 2017 2:18 PMSQL doesn't by default have an "order" to return your result set by. So when you issue a command like "SELECT Transaction_Date FROM Employee_Change", depending on various factors, you could get it in the order you expect, or it could be all jumbled up. Same thing applies when you do an update like that.What I would do first is change your update into a select. This will give you an idea of what it is actually doing and I expect it is not what you think it is doing. When I did that, instead of getting 5 rows, I got 15.
So your logic is wrong with the join. This is easy to see as when the date is 1.1.2017, there are 4 rows which are greater than it so that part of the join will give you 4 rows.So what I think you will want to do is something like this:
WITH cte AS
(
SELECT [Staff_ID] ,
[Transaction_date] ,
[Staff_name] ,
[old_store] ,
[new_store],
ROW_NUMBER() OVER (partition BY Staff_id ORDER BY Transaction_Date) AS [rn]
FROM Employee_Change
)
UPDATE [cte1]
SET [cte1].[New_Store] = [cte2].[Old_Store]
FROM cte cte1
FULL OUTER JOIN cte cte2 ON [cte1].[rn] = [cte2].[rn] + 1I'd recommend doing a "begin transaction" and "rollback transaction" before doing the update though with selects inbetween so you can verify the data.
I don't know the what is means for [rn] and I also got error Invalid column name 'rn' when full outer join is joined.
November 15, 2017 at 7:29 am
rn is the "rn" column from the cte which is the row number partitioned by staff_id, ordered by transaction_Date
What version of SQL are you using? I ran the above on one of my SQL 2008 boxes and it ran without any errors.
I did realize that you will want to change the join to:FULL OUTER JOIN cte cte2 ON [cte1].[rn] = [cte2].[rn] + 1 AND [cte1].[Staff_id] = [cte2].[Staff_id]
As you will want staff ID to be part of your join predicate otherwise you will end up with incorrect results if you have more than 1 staff ID.
But I wrote the above query using your table name so it should just work. You should be able to copy and paste the query exactly as is and run it without any problems.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 19, 2017 at 10:33 pm
Finally I got an answer for this result. I write the query to update one row by row.
UPDATE EC SET Old_Store = ( SELECT TOP 1 New_Store FROM Employee_Change As EC1 WHERE EC1.Staff_Id = EC.Staff_Id And EC1.Transaction_Date < EC.Transaction_Date ORDER BY EC1.Transaction_Date DESC ) FROM Employee_Change As EC
November 20, 2017 at 8:54 am
I am glad you figured it out. I am a little confused why what I wrote didn't work though.
Did you change any of the code I posted? It should have been just a simple copy-paste from what I wrote to your system. No code changes should have been required as it is getting the data from the Employee_Change table.
Also, just to confirm, you were using Microsoft SQL Server 2008 or higher, correct?
It is odd that it would give you an error when you add the join but give you results without the join. What results do you get when you run:WITH cte AS
(
SELECT [Staff_ID] ,
[Transaction_date] ,
[Staff_name] ,
[old_store] ,
[new_store],
ROW_NUMBER() OVER (partition BY Staff_id ORDER BY Transaction_Date) AS [rn]
FROM Employee_Change
)
SELECT *
FROM cte
and when you run:WITH cte AS
(
SELECT [Staff_ID] ,
[Transaction_date] ,
[Staff_name] ,
[old_store] ,
[new_store],
ROW_NUMBER() OVER (partition BY Staff_id ORDER BY Transaction_Date) AS [rn]
FROM Employee_Change
)
SELECT *
FROM cte cte1
FULL OUTER JOIN cte cte2 ON [cte1].[rn] = [cte2].[rn] + 1
I am just trying to figure out why using CTE's didn't work for you. CTE's are quite helpful in SQL.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply