February 2, 2017 at 12:34 am
Hi!
A have two tables, dutyrostershift and timeaccountmovement with a lot of fields -
I am copying from one db to another, and needs to update the owner id of timeaccountmovement.
Its pretty easy to find the correct values - This script gives the interesting values:
SELECT A.ownerid, B.orig_id, B.id FROM dbo.timeaccountmovement A
JOIN dbo.dutyrostershift B ON B.orig_id = A.ownerid
WHERE A.orig_owner_id = 9999999
whicg gives
ownerid orig_id id
2601880 2601880 2947400
2601882 2601882 2947401
2601883 2601883 2947402
2601883 2601883 2947402
+ 674 rows
So, I want to update dbo.timeaccountmovement A setting A.ownerid = B.id
How to?
Best rregards
Edvard Korsbæk
February 2, 2017 at 12:57 am
UPDATE TimeAccountMovement
SET ownerID = DutyRosterShift
WHERE A.orig_owner_ID = 999999
AND B.Orig_id = A.OwnerID;
February 2, 2017 at 1:00 am
Some sample data in an easily run format that covers the scenario and gives the result set you've shown would be helpful. See Jeff Moden's guide:
Forum Etiquette: How to post data/code on a forum to get the best help
Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.
February 2, 2017 at 1:10 am
pietlinden - Thursday, February 2, 2017 12:57 AMUPDATE TimeAccountMovement
SET ownerID = DutyRosterShift
WHERE A.orig_owner_ID = 999999
AND B.Orig_id = A.OwnerID;
Hi!
Really not understood.
This update does not know anything about whats A. and B. etc.
???????????????
Best Wishes
Edvard Korsbæk
February 2, 2017 at 1:13 am
@pietlinden - I think your script's missing a line.
Possibly something on the lines of this would be more complete:
UPDATE A
SET A.ownerid = B.id
FROM dbo.TimeAccountMovement A
JOIN dbo.DutyRosterShift B ON B.orig_id = A.ownerid
WHERE A.orig_owner_id = 9999999;
Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.
February 2, 2017 at 1:26 am
Thanks -
did the trick.
The Update A is the idea I see.
Where do I find something that learns me 'How to' and understand a bit deeper in this regard?
Best regards
Edvard Korsbæk
February 2, 2017 at 1:36 am
Do you mean Forum Etiquette: How to post data/code on a forum to get the best help (this should be a clickable link)? If so click the link (also given in my first post), or if that doesn't work then use the search box at the top of this page and use search term: forum etiquette "post data".
Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.
February 2, 2017 at 2:41 am
Edvard Korsbæk - Thursday, February 2, 2017 1:26 AMThanks -
did the trick.
The Update A is the idea I see.Where do I find something that learns me 'How to' and understand a bit deeper in this regard?
Best regards
Edvard Korsbæk
Edvard
Kudos to you for seeking to understand and not just blindly use code posted on the internet!
This is quite a muddy area, and you can find yourself getting stuck if you're not careful. The UPDATE...FROM syntax can produce unpredictable results if the row you're updating has more than one distinct corresponding match in the table you're updating from. You can use the MERGE statement instead (although that has been reported to have bugs in it - not sure whether they've been fixed). Or you can use the ANSI compliant syntax, something like this:UPDATE TableA
SET Col1 = (
SELECT Col1
FROM TableB AS b
WHERE b.ID = TableA.ID
)
With that construct, you'll get an error message if you have several matches.
Putting all that aside, I'm a little bit concerned that you're attempting to update the column you're joining on. Without being able to see the structure of the tables, the relationship between them, and what's in them, it's difficult to tell whether that's a problem or not. If you could supply that information in the way described in the article that Colin linked to (please script out any foreign key constraints as well), we'll be able to advise. Since you have a lot of columns in each table, feel free to simplify. You seem to suggest the tables are in separate databases, but that's not reflected in your query?
John
February 2, 2017 at 10:40 pm
Thanks everybody!
Yes, I am trying to understand when I am seeking for solutions.
I know the etiquette rules, and most times I try to live up to them.
This time anyway, the tables are so 'Muddy', and I am anyway altering them a couple of times in the process, so i hoped to get an answer on a very direct question, which I have got. "Update Table A set (.................", which for me is a new way of thinking.
I have two databases:
Easyplan_Drift (Drift = production)
Easyplan _test
Someone had on an error 52 (error 40 for a bit overweight!) deleted everything for an employee, but they were still in the test enviroment.
Ok - restore it from the _test to the _prod.
I had most up and running, and i knew excactly how many post i had inserted (givem them the value 999999 in a temp field made that easy.)
Just needed to update the inserted values in the _drift to reflect the new identities created.
I am aware, that update can be a bit unpredictable, but when i saw, that i got exact the correct number of fields updated, and the program that uses the DB shoved that I had the correct posts on app 100 places which i did look at, I wasatisfied.
The full script was (Most was a taken from a former employee with some small twists. He had given up on timeaccountmovement table anyway, and here i just had to find out):
alter table [easyplan_test].[dbo].[dutyrostershift]
add [orig_id] [int] NULL
alter table [easyplan_test].[dbo].[dutyrostershifthisto]
add [orig_id] [int] NULL
alter table [easyplan_test].[dbo].[dutyrostercomment]
add [orig_id] [int] NULL
ALTER TABLE [easyplan_test].[dbo].[timeaccountmovement]
ADD[Orig_Owner_ID] INT null
alter table [easyplan_drift].[dbo].[dutyrostershift]
add [orig_id] [int] NULL
alter table [easyplan_drift].[dbo].[dutyrostershifthisto]
add [orig_id] [int] NULL
alter table [easyplan_drift].[dbo].[dutyrostercomment]
add [orig_id] [int] NULL
ALTER TABLE [easyplan_drift].[dbo].[timeaccountmovement]
ADD[Orig_Owner_ID] INT null
GO
update [easyplan_test].[dbo].[dutyrostershift] set orig_id = id
go
update [easyplan_test].[dbo].[DutyRosterShiftHisto] set orig_id = id
go
update [easyplan_test].[dbo].[dutyrostercomment] set orig_id = id
GO
UPDATE [easyplan_test].[dbo].[timeaccountmovement] SET Orig_Owner_ID = ownerid
DECLARE @NyID INT
SELECT @NyID = 609
INSERT INTO [easyplan_drift].[dbo].[dutyrostershift]
([dato]
,[std]
,[specialvagt]
,[daekbemand]
,[extratimer]
,[overarbtimer]
,[manuel]
,[beskyttet]
,[confirmed]
,[vacationtype]
,[breakswish]
,[dutyrosterid]
,[employeeid]
,[employeegroupid]
,[childforcaredayid]
,[originatingstaffingrequirementid]
,[shifttype]
,[fromtime]
,[totime]
,[LoginID]
,[StatusNo]
,[Time_Stamp]
,[Comment]
,[Is_Free_sat]
,[Is_Center_Opening]
,[orig_id])
SELECT [dato]
,[std]
,[specialvagt]
,[daekbemand]
,[extratimer]
,[overarbtimer]
,[manuel]
,[beskyttet]
,[confirmed]
,[vacationtype]
,[breakswish]
,@NyID
,[employeeid]
,[employeegroupid]
,[childforcaredayid]
,[originatingstaffingrequirementid]
,[shifttype]
,[fromtime]
,[totime]
,[LoginID]
,[StatusNo]
,[Time_Stamp]
,[Comment]
,[Is_Free_sat]
,[Is_Center_Opening]
,[orig_id]
FROM [easyplan_test].[dbo].[dutyrostershift] where dutyrosterid = 609 and employeeid = 1697
GO
Print('Restore duties')
INSERT INTO [easyplan_drift].[dbo].[DutyRosterShiftHisto]
([Comment]
,[Time_Stamp]
,[StatusNo]
,[LoginID]
,[Std]
,[SpecialVagt]
,[DaekBemand]
,[ExtraTimer]
,[Manuel]
,[VacationType]
,[DutyRosterShiftId]
,[EmployeeId]
,[EmployeeGroupId]
,[ChildForCareDayId]
,[ShiftType]
,[FromTime]
,[ToTime]
,[orig_id])
SELECT H.[Comment]
,H.[Time_Stamp]
,H.[StatusNo]
,H.[LoginID]
,H.[Std]
,H.[SpecialVagt]
,H.[DaekBemand]
,H.[ExtraTimer]
,H.[Manuel]
,H.[VacationType]
,H.[DutyRosterShiftId]
,H.[EmployeeId]
,H.[EmployeeGroupId]
,H.[ChildForCareDayId]
,H.[ShiftType]
,H.[FromTime]
,H.[ToTime]
,H.[Id]
FROM [easyplan_test].[dbo].[DutyRosterShiftHisto] H join [easyplan_test].[dbo].[DutyRosterShift] S on h.DutyRosterShiftId = s.id where s.dutyrosterid = 609 AND s.employeeid = 1697
GO
Print('Restore histo')
INSERT INTO [Easyplan_drift].[dbo].timeaccountmovement
( timeaccountid ,
ownerid ,
ownertype ,
starttime ,
days ,
endtime ,
minutes ,
duration ,
do_not_recalculate ,
workingday,
orig_owner_id
)
Select H.timeaccountid ,
H.ownerid ,
H.ownertype ,
H.starttime ,
H.days ,
H.endtime ,
H.minutes ,
H.duration ,
H.do_not_recalculate ,
H.workingday,
9999999
FROM
[easyplan_test].dbo.timeaccountmovement H WHERE ownertype = 1 AND ownerid IN
(
SELECT id FROM [easyplan_test].dbo.dutyrostershift WHERE dato BETWEEN '20160801' AND '20161231' AND employeeid = 1697 AND dutyrosterid = 609
)
go
UPDATE A
SET A.ownerid = B.id
FROM [easyplan_drift].dbo.TimeAccountMovement A
JOIN [easyplan_drift].dbo.DutyRosterShift B ON B.orig_id = A.ownerid
WHERE A.orig_owner_id = 9999999;
PRINT('Tables inserted and updated')
go
alter table [easyplan_test].[dbo].[dutyrostershift]
drop COLUMN [orig_id]
alter table [easyplan_test].[dbo].[dutyrostershifthisto]
drop COLUMN [orig_id]
alter table [easyplan_test].[dbo].[dutyrostercomment]
drop COLUMN [orig_id]
alter table [easyplan_drift].[dbo].[dutyrostershift]
drop COLUMN [orig_id]
alter table [easyplan_drift].[dbo].[dutyrostershifthisto]
drop COLUMN[orig_id]
alter table [easyplan_drift].[dbo].[dutyrostercomment]
drop COLUMN [orig_id]
PRINT('Reset tables')
GO
with this result:
(798171 row(s) affected)
(2209471 row(s) affected)
(1981 row(s) affected)
(1314924 row(s) affected)
(499 row(s) affected)
Restore duties
(733 row(s) affected)
Restore histo
(339 row(s) affected)
(339 row(s) affected)
Tables inserted and updated
Reset tables
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply