SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


change old store to new store


change old store to new store

Author
Message
sanshwemyat.ssm
sanshwemyat.ssm
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
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

bmg002
bmg002
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11577 Visits: 2222
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.

sanshwemyat.ssm
sanshwemyat.ssm
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
bmg002 - Tuesday, November 14, 2017 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.

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.

bmg002
bmg002
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11577 Visits: 2222
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.
sanshwemyat.ssm
sanshwemyat.ssm
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
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

bmg002
bmg002
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11577 Visits: 2222
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search