Need help on Update statment with where condition

• Qira

  SSCommitted

  Points: 1930

  More actions

  August 23, 2014 at 8:10 am

  #397250

  I've table and data as follow,

  CREATE TABLE [dbo].[x_Score](

  [idx] [int] IDENTITY(1,1) NOT NULL,

  [ApplyJob] [varchar](1000) NULL,

  [r_ApplyJob] [varchar](1000) NULL,

  [score] [tinyint] NULL,

  CONSTRAINT [PK_x_Score] PRIMARY KEY CLUSTERED

  (

  [idx] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  SET ANSI_PADDING OFF

  GO

  SET IDENTITY_INSERT [dbo].[x_Score] ON

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (1, N' 4', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (2, N' 3, 47, 46, 12, 11, 36', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (3, N' 2', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (4, N' 2, 11', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (5, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (6, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (7, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (8, N' 3, 46', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (9, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (10, N' 3, 8, 7, 25, 41, 48', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (11, N' 22', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (12, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (13, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (14, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (15, N' 3, 2, 6, 7, 11, 47, 46', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (16, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (17, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (18, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (19, N' 39', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (20, N' 3, 2', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (21, N' 3, 2', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (22, N' 47', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (23, N' 3, 11', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (24, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (25, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (26, N' 3, 47', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (27, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (28, N' 3, 11', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (29, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (30, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (31, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (32, N' 3, 2, 38, 39', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (33, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (34, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (35, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (36, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (37, N' 38', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (38, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (39, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (40, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (41, N' 38', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (42, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (43, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (44, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (45, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (46, N' 2, 12, 11, 16', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (47, N' 2', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (48, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (49, N' 3, 45, 46', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (50, N' 3, 2, 47', N' 72, 75, 29, 35, 61', 0)

  SET IDENTITY_INSERT [dbo].[x_Score] OFF

  /****** Object: Default [DF_x_Score_score] Script Date: 08/23/2014 22:03:09 ******/

  ALTER TABLE [dbo].[x_Score] ADD CONSTRAINT [DF_x_Score_score] DEFAULT ((0)) FOR [score]

  GO

  *r_ApplyJob is SAME

  Here the logic,

  1- Where idx=10, Numeric in ApplyJob ( 3, 8, 7, 25, 41, 48) EXIST in r_ApplyJob --> SET score=10

  2- Where idx=16, Numeric in ApplyJob (NULL) EXIST in r_ApplyJob --> NO NEED TO SET score

  3- 2- Where idx=34, Numeric in ApplyJob (3) EXIST in r_ApplyJob --> SET score=10

  * score=10 is SAME

  Need help to build UPDATE statement. Please help

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 23, 2014 at 9:22 am

  #1740470

  What are you trying to do?

  Your description is rather vague...

  For instance, what do you mean by

  Where idx=34, Numeric in ApplyJob (3) EXIST in r_ApplyJob --> SET score=10

  ??

  For that idx value there's no '3' in r_ApplyJob. Why change the core?

  Why do you store numeric values as a list in a single column?

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Qira

  SSCommitted

  Points: 1930

  More actions

  August 23, 2014 at 9:33 am

  #1740472

  LutzM (8/23/2014)

  ---

  What are you trying to do?

  Your description is rather vague...

  For instance, what do you mean by

  Where idx=34, Numeric in ApplyJob (3) EXIST in r_ApplyJob --> SET score=10

  ??

  For that idx value there's no '3' in r_ApplyJob. Why change the core?

  Why do you store numeric values as a list in a single column?

  This is my explanation sir..

  1- Idx is a primary key

  2- Whatever numeric in ApplyJob that EXISTS in r_ApplyJob, please set score=10

  3- ApplyJob format is using

  SELECT [CVID]

  , STUFF((SELECT ', ' + Convert(varchar(10),A.[JobNoticeID])

  FROM JobNoticeCV A

  Where A.[CVID]=B.[CVID] FOR XML PATH('')),1,1,'') As [All_JobNoticeID]

  ..........

  How to check figure in ApplyJob is EXISTS in r_ApplyJob?? If one of figure is found, then set SCORE = 10

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 23, 2014 at 9:41 am

  #1740473

  Unfortunately, you didn't answer my question:

  Why would idx=34 get a score of 10?

  It would be a lot easier if the data where in normalized form instead of a separated list. This makes the solution more complex than working with the apparently original data (JobNoticeCV.JobNoticeID]) ...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Qira

  SSCommitted

  Points: 1930

  More actions

  August 23, 2014 at 10:00 am

  #1740476

  LutzM (8/23/2014)

  ---

  Unfortunately, you didn't answer my question:

  Why would idx=34 get a score of 10?

  It would be a lot easier if the data where in normalized form instead of a separated list. This makes the solution more complex than working with the apparently original data (JobNoticeCV.JobNoticeID]) ...

  Owh, that the mistake.

  idx=34 and ApplyJob=3 NOT EXISTS in r_ApplyJob. So, SCORE is maintained as 0.

  Sorry my bad.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 23, 2014 at 10:13 am

  #1740477

  Qira (8/23/2014)

  ---

  I've table and data as follow,

  CREATE TABLE [dbo].[x_Score](

  [idx] [int] IDENTITY(1,1) NOT NULL,

  [ApplyJob] [varchar](1000) NULL,

  [r_ApplyJob] [varchar](1000) NULL,

  [score] [tinyint] NULL,

  CONSTRAINT [PK_x_Score] PRIMARY KEY CLUSTERED

  (

  [idx] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  SET ANSI_PADDING OFF

  GO

  SET IDENTITY_INSERT [dbo].[x_Score] ON

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (1, N' 4', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (2, N' 3, 47, 46, 12, 11, 36', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (3, N' 2', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (4, N' 2, 11', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (5, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (6, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (7, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (8, N' 3, 46', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (9, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (10, N' 3, 8, 7, 25, 41, 48', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (11, N' 22', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (12, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (13, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (14, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (15, N' 3, 2, 6, 7, 11, 47, 46', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (16, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (17, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (18, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (19, N' 39', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (20, N' 3, 2', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (21, N' 3, 2', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (22, N' 47', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (23, N' 3, 11', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (24, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (25, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (26, N' 3, 47', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (27, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (28, N' 3, 11', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (29, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (30, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (31, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (32, N' 3, 2, 38, 39', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (33, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (34, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (35, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (36, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (37, N' 38', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (38, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (39, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (40, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (41, N' 38', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (42, NULL, N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (43, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (44, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (45, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (46, N' 2, 12, 11, 16', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (47, N' 2', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (48, N' 3', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (49, N' 3, 45, 46', N' 72, 75, 29, 35, 61', 0)

  INSERT [dbo].[x_Score] ([idx], [ApplyJob], [r_ApplyJob], [score]) VALUES (50, N' 3, 2, 47', N' 72, 75, 29, 35, 61', 0)

  SET IDENTITY_INSERT [dbo].[x_Score] OFF

  /****** Object: Default [DF_x_Score_score] Script Date: 08/23/2014 22:03:09 ******/

  ALTER TABLE [dbo].[x_Score] ADD CONSTRAINT [DF_x_Score_score] DEFAULT ((0)) FOR [score]

  GO

  *r_ApplyJob is SAME

  Here the logic,

  1- Where idx=10, Numeric in ApplyJob ( 3, 8, 7, 25, 41, 48) EXIST in r_ApplyJob --> SET score=10

  2- Where idx=16, Numeric in ApplyJob (NULL) EXIST in r_ApplyJob --> NO NEED TO SET score

  3- 2- Where idx=34, Numeric in ApplyJob (3) EXIST in r_ApplyJob --> SET score=10

  * score=10 is SAME

  Need help to build UPDATE statement. Please help

  You've provided us with a before picture, now show us what the data should be like when the updates are completed. Provide another table and insert statements so we have something to compare against.

  My problem is trying to visualize the changes based on your simple word descriptions of the updates.

• Qira

  SSCommitted

  Points: 1930

  More actions

  August 23, 2014 at 11:02 am

  #1740484

  Ok. This is my table

  1- x_CV

  CREATE TABLE [dbo].[x_CV](

  [CVID] [int] NOT NULL,

  [point_To_Score] [tinyint] NULL,

  [score] [tinyint] NULL,

  CONSTRAINT [PK_x_CV] PRIMARY KEY CLUSTERED

  (

  [CVID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (72, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (76, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (77, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (78, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (79, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (88, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (90, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (92, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (93, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (97, 10, NULL)

  2- x_ApplyJob (CVID apply job save in this table)

  CREATE TABLE [dbo].[x_ApplyJob](

  [CVID] [int] NOT NULL,

  [JobNoticeID] [int] NOT NULL,

  CONSTRAINT [x_ApplyJob_UQ1] UNIQUE NONCLUSTERED

  (

  [CVID] ASC,

  [JobNoticeID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (72, 21)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (72, 22)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (76, 21)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (76, 45)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (88, 24)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (88, 45)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (90, 12)

  /****** Object: ForeignKey [FK_x_ApplyJob_x_CV] Script Date: 08/24/2014 00:54:56 ******/

  ALTER TABLE [dbo].[x_ApplyJob] WITH CHECK ADD CONSTRAINT [FK_x_ApplyJob_x_CV] FOREIGN KEY([CVID])

  REFERENCES [dbo].[x_CV] ([CVID])

  GO

  ALTER TABLE [dbo].[x_ApplyJob] CHECK CONSTRAINT [FK_x_ApplyJob_x_CV]

  GO

  3- z_JobChecking (if Job Applied EXISTS in this table, please set score = point_To_Score based on CVID. JobCheckingID is unique)

  CREATE TABLE [dbo].[z_JobChecking](

  [JobCheckingID] [int] NULL

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (22)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (45)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (100)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (49)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (241)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (56)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (4)

  Please help

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 23, 2014 at 11:12 am

  #1740487

  Qira (8/23/2014)

  ---

  Ok. This is my table

  1- x_CV

  CREATE TABLE [dbo].[x_CV](

  [CVID] [int] NOT NULL,

  [point_To_Score] [tinyint] NULL,

  [score] [tinyint] NULL,

  CONSTRAINT [PK_x_CV] PRIMARY KEY CLUSTERED

  (

  [CVID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (72, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (76, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (77, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (78, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (79, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (88, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (90, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (92, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (93, 10, NULL)

  INSERT [dbo].[x_CV] ([CVID], [point_To_Score], [score]) VALUES (97, 10, NULL)

  2- x_ApplyJob (CVID apply job save in this table)

  CREATE TABLE [dbo].[x_ApplyJob](

  [CVID] [int] NOT NULL,

  [JobNoticeID] [int] NOT NULL,

  CONSTRAINT [x_ApplyJob_UQ1] UNIQUE NONCLUSTERED

  (

  [CVID] ASC,

  [JobNoticeID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (72, 21)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (72, 22)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (76, 21)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (76, 45)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (88, 24)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (88, 45)

  INSERT [dbo].[x_ApplyJob] ([CVID], [JobNoticeID]) VALUES (90, 12)

  /****** Object: ForeignKey [FK_x_ApplyJob_x_CV] Script Date: 08/24/2014 00:54:56 ******/

  ALTER TABLE [dbo].[x_ApplyJob] WITH CHECK ADD CONSTRAINT [FK_x_ApplyJob_x_CV] FOREIGN KEY([CVID])

  REFERENCES [dbo].[x_CV] ([CVID])

  GO

  ALTER TABLE [dbo].[x_ApplyJob] CHECK CONSTRAINT [FK_x_ApplyJob_x_CV]

  GO

  3- z_JobChecking (if Job Applied EXISTS in this table, please set score = point_To_Score based on CVID. JobCheckingID is unique)

  CREATE TABLE [dbo].[z_JobChecking](

  [JobCheckingID] [int] NULL

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (22)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (45)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (100)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (49)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (241)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (56)

  INSERT [dbo].[z_JobChecking] ([JobCheckingID]) VALUES (4)

  Please help

  What about the table you mention in your first post? I thought that was the table being updated?

  Looks to me like you are changing your requirements and I still don't see any expected results based on sample data after the update(s) are completed.

• Qira

  SSCommitted

  Points: 1930

  More actions

  August 23, 2014 at 11:48 am

  #1740491

  This is the result after Update

  CVID point_To_Scorescore

  72 10 10

  76 10 10

  77 10 NULL

  78 10 NULL

  79 10 NULL

  88 10 10

  90 10 NULL

  92 10 NULL

  93 10 NULL

  97 10 NULL

  This is the explanation. See x_ApplyJob

  1- CVID=72 have JobNoticeID that EXISTS in z_JobChecking. It's 22. One or more record EXISTS is enough

  2- CVID=76 have JobNoticeID that EXISTS in z_JobChecking. It's 45. One or more record EXISTS is enough

  3- CVID=88 have JobNoticeID that EXISTS in z_JobChecking. It's 45. One or more record EXISTS is enough

  4- CVID=90 do not have JobNoticeID that EXISTS in z_JobChecking. So, no score for this row

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 23, 2014 at 12:56 pm

  #1740494

  What about the table you mention in your first post? I thought that was the table being updated?

  Looks to me like you are changing your requirements and I still don't see any expected results based on sample data after the update(s) are completed.

  @Lynn: The requirements didn't really change.

  I've asked the OP to post the "source table" where the comma separated list is derived from.

  So, it's "my fault" 😉

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 23, 2014 at 1:16 pm

  #1740495

  Are you looking for something along those lines?

  WITH cte as

  (

  SELECT DISTINCT cvid FROM [x_ApplyJob] a

  WHERE EXISTS (SELECT 1 FROM [z_JobChecking] c WHERE c.JobCheckingID = a.JobNoticeID)

  )

  UPDATE cv

  SET score = 10

  FROM cte

  INNER JOIN [dbo].[x_CV] cv on cte.cvid = cv.cvid

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]