Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to script an conditional update Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 7:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 91, Visits: 159
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

Post #1498326
Posted Wednesday, September 25, 2013 7:15 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 4,061, Visits: 3,497
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1498339
Posted Wednesday, September 25, 2013 7:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 91, Visits: 159
Thanks!

so easy - I learned that its a good idea to use [table name].[FieldName] in queries here.

Thanks for that too.

Best regards

Edvard Korsbæk
Post #1498366
Posted Wednesday, September 25, 2013 7:53 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 4,061, Visits: 3,497
No problem. Glad I could help.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1498370
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse