September 11, 2014 at 8:58 am
Hi All!
Must be easy, but none here has found the solution.
I have a table called dutyrostershift with + 100 fields . The only interesting fields is:
ID int identity(1,1)
Orig ID INT
The other table i have is:
CREATE TABLE [dbo].[timeaccountmovement](
[timeaccountid] [int] NULL,
[ownerid] [int] NULL,
[ownertype] [int] NULL,
[starttime] [int] NULL,
[days] [int] NULL,
[endtime] [int] NULL,
[minutes] [int] NULL,
[duration] [varchar](20) NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[do_not_recalculate] [tinyint] NULL,
[DATO] [datetime] NULL,
CONSTRAINT [pk_dbo_pk_timeaccountmovement] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The interesting field here is:
[ownerid] [int] NULL,
This field contains ID from Dutyrostershift.
I have copied a lot of dutyrostershifts to a new position.
That means, that they have got a new id.
The old ID is in orig_id
I know, that one of the orig_id's is 1480002
This query, gives the new id
SELECT [dato]
,[id]
,[orig_id]
FROM [a_rc_drift].[dbo].[dutyrostershift] where dutyrosterid = 287 and orig_id = 1480002
from result set,:
ID = 1513160
So this query, updates the timeaccountmovement, which belonged to 1480002:
Update a_rc_drift.dbo.timeaccountmovement SET ownerid = 1513160 where ownerid = 1480002
My problem is, that i need to update app. 2500 records.
So, the query is something like:
Update timeaccountmovement set ownerid = id from dutyrostershift where orig_id = ownerid
This won't work - At lest there should be a select after =, but we have not been able to figure out 'How to'
In words:
set ownerid in timeaccountmovement to id from dyturostershift where orig_id in dutyrostershift equals ownerid in timeaccountmovement
Best regards
Edvard Korsbæk
September 11, 2014 at 9:11 am
I got totally lost in the explanation and you don't really seem to have a question. Can you put together ddl and sample data along with desired output so we are clear on what you are trying to do? I think maybe all you need is to use a join in your update statement but I am not at all certain from the description.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2014 at 10:19 am
dutyrostershift:
CREATE TABLE [dbo].[dutyrostershift](
[dato] [datetime] NULL,
[std] [tinyint] NULL,
[specialvagt] [tinyint] NULL,
[daekbemand] [tinyint] NULL,
[extratimer] [int] NULL,
[overarbtimer] [int] NULL,
[manuel] [tinyint] NULL,
[beskyttet] [tinyint] NULL,
[confirmed] [tinyint] NULL,
[vacationtype] [varchar](50) NULL,
[breakswish] [tinyint] NULL,
[dutyrosterid] [int] NULL,
[employeeid] [int] NULL,
[employeegroupid] [int] NULL,
[childforcaredayid] [int] NULL,
[originatingstaffingrequirementid] [int] NULL,
[shifttype] [int] NULL,
[fromtime] [int] NULL,
[totime] [int] NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[leavetype_id] [int] NULL,
[LoginID] [int] NULL,
[StatusNo] [int] NULL,
[Time_Stamp] [datetime] NULL,
[Comment] [char](120) NULL,
[Is_Free_sat] [tinyint] NULL,
[Is_Center_Opening] [tinyint] NULL,
[is_fo_day] [tinyint] NULL,
[SavedDuty_Id] [int] NULL,
[mTid_Id] [int] NULL,
[duty_released] [tinyint] NULL,
[orig_id] [INT] NULL
CONSTRAINT [pk_dbo_pk_dutyrostershift] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Timeaccountmovement:
CREATE TABLE [dbo].[timeaccountmovement](
[timeaccountid] [int] NULL,
[ownerid] [int] NULL,
[ownertype] [int] NULL,
[starttime] [int] NULL,
[days] [int] NULL,
[endtime] [int] NULL,
[minutes] [int] NULL,
[duration] [varchar](20) NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[do_not_recalculate] [tinyint] NULL,
[DATO] [datetime] NULL,
CONSTRAINT [pk_dbo_pk_timeaccountmovement] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Question:
how to update records, so timeaccountmovement.ownerid = dutyrosteshift.id where dutyrostershift.orig_id = timeaccountmovement.ownerid
or how to shift the coeesponding timeaccountmovement.ownerid from orig_id to ID from dutyrostershift.
Hope this is better.
Best regards
Edvard Korsbæk
September 11, 2014 at 10:58 am
Still not 100% sure what you want but I am pretty sure you are looking for something like this.
update t
set ownerid = d.id
from timeaccountmovement t
join dutyrostershift d on d.orig_id = t.ownerid
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2014 at 10:10 pm
Thanks!
Did the trick,
Best regards
Edvard Korsbæk
September 12, 2014 at 7:03 am
Edvard Korsbæk (9/11/2014)
Thanks!Did the trick,
Best regards
Edvard Korsbæk
Great. Glad that worked for you and thanks for letting me know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply