November 21, 2014 at 9:25 am
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,Airline VARCHAR(50) NOT NULL
,Aircraft1 VARCHAR(50) NOT NULL
,Aircraft1_unsub VARCHAR(50) NOT NULL
,Aircraft2 VARCHAR(50) NOT NULL,
Aircraft2_unsub varchar(50) Not null
)
INSERT INTO dbo.TBL_SAMPLE_DATA
(
Airline
,Aircraft1
,Aircraft1_unsub
,Aircraft2,
Aircraft2_unsub
)
VALUES
('AZ','y','y','n','n')
,('BA','n','N','N','Y')
,('DL','Y','Y','Y','Y')
,('AA','N','Y','Y','N')
,('NW','Y','N','N','N')
,('KL','N','N','N','N')
;
I have to compare the Aircraft1 and Aircraft1_unsub based on condition as below:
[case
when Aircraft1='N' or Aircraft1_unsub='Y' then 0
else 1
end ]
Based on the comparision output i have to update the main table with the outputvalue for each Aircraft based on the Airline
update t set t.Aircraft1=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline
update t set t.Aircraft2=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline
Can anyone help with the most efficient way of doing this operation. Thanks in advance for looking into my post.
November 21, 2014 at 9:50 am
Your question is not very clear. You did a great job posting ddl and sample data for the sample data table. However, your query has the table main_table in it but there is no information about this table anywhere. Also, it is not at all clear what you are trying to do. Help us understand the problem and we can help you with a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 21, 2014 at 12:06 pm
Sean Lange (11/21/2014)
Your question is not very clear. You did a great job posting ddl and sample data for the sample data table. However, your query has the table main_table in it but there is no information about this table anywhere. Also, it is not at all clear what you are trying to do. Help us understand the problem and we can help you with a solution.
Sorry i was in a hurry.
Detailed question is as below:
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,logon VARCHAR(50) NOT NULL
,newsletter1 VARCHAR(50) NOT NULL
,newsletter1_unsub VARCHAR(50) NOT NULL
,newsletter2 VARCHAR(50) NOT NULL,
newsletter2_unsub varchar(50) Not null
)
INSERT INTO dbo.TBL_SAMPLE_DATA
(
logon
,newsletter1
,newsletter1_unsub
,newsletter2,
newsletter2_unsub
)
VALUES
('john','y','y','n','n')
,('david','n','N','N','Y')
,('don','Y','Y','Y','Y')
,('chad','N','Y','Y','N')
,('chet','Y','N','N','N')
,('chris','N','N','N','N')
;
select * from TBL_SAMPLE_DATA
I have to compare the newsletter1 and newsletter1_unsub for each logon_useron based on condition as below:
[case
when newsletter1='N' or newsletter1_unsub='Y' then usr_status_id= 0
else usr_status_id=1
end ]
Below is the newsletter lookuptable
Here i have mentioned only 2 newsletter for sample but i have more than 10 newsletter to compare for single logonuser.
CREATE TABLE [dbo].[newsletter_LOOKUP](
[ID] [int] IDENTITY(1,1) NOT NULL,
[glb_mailing_list_id] [int] NULL,
[nvarchar](255) NULL,
CONSTRAINT [PK_BRAND_mailingid_LOOKUP] PRIMARY KEY CLUSTERED
(
[ID] 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 INTO [TEST].[dbo].[newsletter_LOOKUP]
([glb_mailing_list_id]
,
)
VALUES
(1, 'newsletter1'),
(2, 'newsletter2')
GO
CREATE TABLE [dbo].[mailing_list](
[usr_mailing_id] [int] IDENTITY(1,1) NOT NULL,
logon VARCHAR(50) NOT NULL,
[glb_mailing_list_id] [int] NOT NULL,
[usr_status_id] [int] NOT NULL
)
So i have to update the mailing_list table with the usr_status_id for that logon as below:
update sub set sub.usr_status_id=outputvalue(0 or 1)
from [mailing_list] sub
inner join TBL_SAMPLE_DATA t
on sub.logon=t.logon
where sub.[glb_mailing_list_id]=1
update sub set sub.usr_status_id=outputvalue(0 or 1)
from [mailing_list] sub
inner join TBL_SAMPLE_DATA t
on sub.logon=t.logon
where sub.[glb_mailing_list_id]=2
November 21, 2014 at 12:35 pm
muthyala_51 (11/21/2014)
Sean Lange (11/21/2014)
Your question is not very clear. You did a great job posting ddl and sample data for the sample data table. However, your query has the table main_table in it but there is no information about this table anywhere. Also, it is not at all clear what you are trying to do. Help us understand the problem and we can help you with a solution.Sorry i was in a hurry.
You being in a hurry is causing you to post useless information. We have some tables and data but there is nothing explaining what you want to do. You need to slow down and read what you are posting. Keep in mind I can't see your screen, I have no idea what your data is like and I have no idea what your process is. Help us to help you by providing the details. Without it we are guessing and are highly unlikely to even get close to what you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 21, 2014 at 1:04 pm
sorry again.
Here is the requirement:
I am getting some input file with all the logons who have subscribed for the different newsletters in an excel file which i am importing into the TBL_sample_data.
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
logon VARCHAR(50) NOT NULL
,newsletter1 VARCHAR(50) NOT NULL
,newsletter1_unsub VARCHAR(50) NOT NULL
,newsletter2 VARCHAR(50) NOT NULL,
newsletter2_unsub varchar(50) Not null,
newsletter3 VARCHAR(50) NOT NULL,
Newsletter3_unsub varchar(50) Not null,
Newsletter4 VARCHAR(50) NOT NULL,
Newsletter4_unsub varchar(50) Not null,
)
INSERT INTO [CKAPRIMO].[dbo].[TBL_SAMPLE_DATA]
([logon]
,[newsletter1]
,[newsletter1_unsub]
,[newsletter2]
,[newsletter2_unsub]
,[newsletter3]
,[Newsletter3_unsub]
,[Newsletter4]
,[Newsletter4_unsub])
VALUES
('john','y','y','n','n','y','y','N','N')
,('david','n','N','N','Y','N','y','N','N')
,('don','Y','Y','Y','Y','N','y','Y','N')
,('chad','N','Y','Y','N','y','y','y','y')
,('chet','Y','N','N','N','N','N','N','N')
,('chris','N','N','N','N','y','y','Y','Y')
,('tom','Y','Y','Y','N','y','y','Y','Y')
GO
So for every logon column in table TBL_sample_data, we have columns
newsletter1(value is 'Y' meaning that the user has subscribed to the newsletter1, value N meaning not subscribed)
newsletter_unsub (value is 'Y' meaning that the user has unsubscribed to the newsletter1), same likely i have nearly 12 other newsletters(newsletter2, newsletter2_unsub,newsletter3, newslette3r_unsub etc) in that imported table.
But the data in that TBL_sample_data is off, so based on condition as below for each logon we have to update the Mailing_list table
as
[when newsletter1='N' or newsletter1_unsub='Y' then usr_status_id= 0
else usr_status_id should be set to 1
for that Logon]
The main table which holds the newsletter subscription in our database is "[mailing_list]" which i have mentioned schema as below:
CREATE TABLE [dbo].[mailing_list](
[usr_mailing_id] [int] IDENTITY(1,1) NOT NULL,
logon VARCHAR(50) NOT NULL,
[glb_mailing_list_id] [int] NOT NULL,
[usr_status_id] [int] NOT NULL
)
Here for every newsletter there is an associated mapping glb_mailing_list_id(int) , which i will be using as lookup table to map the newsletter with the glb_mailing_list in order to upate the main_table.
Lookup table is as below:
CREATE TABLE [dbo].[newsletter_LOOKUP](
[ID] [int] IDENTITY(1,1) NOT NULL,
[glb_mailing_list_id] [int] NULL,
[nvarchar](255) NULL,
CONSTRAINT [PK_mailingid_LOOKUP] PRIMARY KEY CLUSTERED
(
[ID] 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 INTO [TEST].[dbo].[newsletter_LOOKUP]
([glb_mailing_list_id]
,
)
VALUES
(1, 'newsletter1'),
(2, 'newsletter2'),
(30, 'newsletter3'),
(40, 'newsletter4'),
GO
So i have to update the mailing_list table with the usr_status_id for that logon as below:
Update sub
set sub.usr_status_id=case when newsletter1='N' or newsletter1_unsub='Y' then 0
else 1
end
from [mailing_list] sub
inner join TBL_SAMPLE_DATA t
on sub.logon=t.logon
where sub.[glb_mailing_list_id]=1
Update sub
set sub.usr_status_id=case when newsletter2='N' or newsletter2_unsub='Y' then 0
else 1
end
from [mailing_list] sub
inner join TBL_SAMPLE_DATA t
on sub.logon=t.logon
where sub.[glb_mailing_list_id]=2
This update i am doing for all 12 newsletters , in a loop through the Tbl_sample_data for each logon user using cursor or temptable one by one row and it is taking a while to process the records. I am looking for good performance solution.
Thanks.
November 21, 2014 at 1:16 pm
muthyala_51 (11/21/2014)
sorry again.Here is the requirement:
...
This update i am doing for all 12 newsletters , in a loop through the Tbl_sample_data for each logon user using cursor or temptable one by one row and it is taking a while to process the records. I am looking for good performance solution.
Thanks.
So you are saying you already have code that does what you want but it is RBAR and you want to make it set based? Post your cursor and I will help you get rid of it. From what you describe this is nothing more than a single update statement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 21, 2014 at 1:43 pm
I am not fully finished with the code and the schema i provided is not the same in our database, i have posted simplifying it. So can you please provide your input on the best solution how to achieve it. Thanks
November 21, 2014 at 1:48 pm
muthyala_51 (11/21/2014)
I am not fully finished with the code and the schema i provided is not the same in our database, i have posted simplifying it. So can you please provide your input on the best solution how to achieve it. Thanks
Without something to work with I would stop looping and turn this into a set based update.
It sounds like you want to change the status_id to 0 when ANY of the subscriptions are not 'Y'?
pseudocode...
update yt
set status_id = case when Sub1 = ' Y' and Sub2 = 'Y' and Sub3 = 'Y' then 1 else 0 end
from YourTable yt
join ....
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply