Need help on Update statment with where condition

Question

Post reply

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

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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 · Answer 5

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 · Answer 6

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 · Answer 7

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 · Answer 8

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 · Answer 9

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 · Answer 10

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]