Need help on Update statment with where condition

  • 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

  • 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]

  • 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

  • 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]

  • 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.

  • 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.

  • 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

  • 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.

  • 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

  • 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]

  • 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]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply