September 25, 2013 at 7:05 am
Hi all clever scripters out there!
I am going to make a one time update after having made a new function.
I have one table
[dbo].[savedduty](
[NAME] [varchar](20) NULL,
[STARTTIME] [int] NULL,
[DAYS] [int] NULL,
[ENDTIME] [int] NULL,
[DAEKBEMAND] [tinyint] NULL,
[EMPLOYEEGROUPID] [int] NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[Selected_Record] [tinyint] NULL,
[Name_Day_2] [char](20) NULL,
[InstitutionsId] [int] NULL,
[Color] [int] NULL,
[Color_R] [tinyint] NULL,
[Color_B] [tinyint] NULL,
[Color_G] [tinyint] NULL,
The important part here is [NAME] and [ID], which is the two components i should use to update this table:
[dbo].[dutyrostershift](
[DATO] [datetime] NULL,
[STD] [tinyint] NULL,
[SPECIALVAGT] [tinyint] NULL,
[DAEKBEMAND] [tinyint] NULL,
[EXTRATIMER] [real] NULL,
[OVERARBTIMER] [real] 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,
The rule is Pretty simple:
If dbo.dutyrostershift.vacationtype = dbo.savedduty.name then dbo.dutyrostershift.savedduty_id = dbo.savedduty.id.
i.e. if vacationtype exists in name from savedduty, update saveddutyroster_id with ID from savedduty
But, how to script this?
Best regards
Edvard Korsbæk
September 25, 2013 at 7:15 am
Is this what you're looking for?
update dutyrostershift
set dutyrostershift.savedduty_id = savedduty.id
from savedduty
where dutyrostershift.vacationtype = savedduty.name;
One thing worth pointing out is that savedduty.name is a varchar(20) and dutyrostershift.vacationtype is a varchar(50), meaning that one field could have values longer than the other and won't be updated. The above update statement will perform correctly, but it might miss some because the values can't match if dutyrostershift.vacationtype is longer than 20.
September 25, 2013 at 7:49 am
Thanks!
so easy - I learned that its a good idea to use
.[FieldName] in queries here.
Thanks for that too.
Best regards
Edvard Korsbæk
September 25, 2013 at 7:53 am
No problem. Glad I could help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply