SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update and Insert statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 10/10/2013 :  12:10:48  Show Profile  Reply with Quote
Hi All
I'm trying to update a table based on some crazy logic and need hellp from the experts. The two columns inDISTRIBUTION_SL table needs to be updated based on the MAINTENANCE_SL table .I ma trying ot be as detial asi can.

Please let me know if i missed anything

TablesXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXCREATE TABLE [dbo].[DISTRIBUTION_SL](
[requestid] [numeric](18, 0) NOT NULL,
[rptdis] [char](1) NOT NULL,
[recipnm] [varchar](50) NULL,
[emailadr] [varchar](75) NULL,
[Seq] [int] NOT NULL,
[phone] [varchar](30) NULL,

CONSTRAINT [PK__REPORT_DISTRIBUTION] PRIMARY KEY CLUSTERED
(
[requestid] ASC,
[rptdis] ASC,
[Seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXCREATE TABLE [dbo].[STOPLOSS_MAINTENANCE](
[recordid] [numeric](18, 0) NOT NULL,
[requestid] [numeric](18, 0) NOT NULL,
[rptdis] [char](1) NULL,
[DATARECIPIENT1NAME] [varchar](50) NULL,
[DATARECIPIENT1EMAIL] [varchar](100) NULL,
[DATARECIPIENT2NAME] [varchar](50) NULL,
[DATARECIPIENT2EMAIL] [varchar](100) NULL,
[DATARECIPIENT3NAME] [varchar](50) NULL,
[DATARECIPIENT3EMAIL] [varchar](100) NULL,
[DATARECIPIENT4NAME] [varchar](50) NULL,
[DATARECIPIENT4EMAIL] [varchar](100) NULL,
[DATARECIPIENT5NAME] [varchar](50) NULL,
[DATARECIPIENT5EMAIL] [varchar](100) NULL,
[DATARECIPIENT6NAME] [varchar](50) NULL,
[DATARECIPIENT6EMAIL] [varchar](100) NULL,
[DATARECIPIENT7NAME] [varchar](50) NULL,
[DATARECIPIENT7EMAIL] [varchar](100) NULL,
[DATARECIPIENT8NAME] [varchar](50) NULL,
[DATARECIPIENT8EMAIL] [varchar](100) NULL,
[DATARECIPIENT9NAME] [varchar](50) NULL,
[DATARECIPIENT9EMAIL] [varchar](100) NULL,
[CURRENTINDICATOR] [char](1) NULL,

PRIMARY KEY CLUSTERED
(
[recordid] ASC,
[requestid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Steps identified in the process

•Find out how many recipients are there for a request by counting the non null columns datarecipient1name, datarecipient2name, datarecipient3name, datarecipient4name, datarecipient5name, datarecipient6name, datarecipient7name, ,datarecipient8name, datarecipient9name, from MAINTENACE_SL Table where requestid in ().
SQL:
Select requestid,rptdis,datarecipient1name,datarecipient1email,rptdis2,datarecipient2name,datarecipient2email,rptdis3,datarecipient3name,datarecipient3email,rptdis4,datarecipient4name,datarecipient4email,rptdis5,datarecipient5name,datarecipient5email,rptdis6,datarecipient6name,datarecipient6email,rptdis7,datarecipient7name,datarecipient7email,rptdis8,datarecipient8name,datarecipient8email,datarecipient9name,datarecipient9email from stoploss_maintenance where requestid = 313524 and currentindicator = 'Y'


Data - The count of non null columns here is 6

313524 B Jennifer XYZ jpool@yahoo.com C Lisa Yaris lisa.y@gmail.com S Heather Matiz heather_m@hotmail.com V Paul Sentra paul@aol.com V Torin xavier elcl@abc.com V Merrylou Schlarb Merrylou.schlarb@symetra.com NULL NULL NULL NULL NULL NULL NULL NULL

• Compare that count to the count of records for that request in DISTRIBUTION_SL Table.

SQL Statement:
select requestid,recipnm,emailadr,seq,phone from DISTRIBUTION_SL where requestid = 313524


Data: The count is 5

313524 Jennifer XYZ jpool@yahoo.com 4 111-111-1111
313524 Lisa Yaris lisa.y@gmail.com 5 222-222-2222
313524 Heather Matiz heather_m@hotmail.com 2 333-333-3333
313524 Paul Sentra paul@aol.com 1 444-444-4444
313524 Torin xavier elcl@abc.com 3 555-555-5555

•Update Query for updating the DISTRIBUTION_SL table based on the number of column count from MAINTENACE_SL.
•Insert new records into DISTRIBUTION_SL Table if the counts don’t match .The number of records that are inserted should be equal to the difference of the count between the column count from MAINTENACE_SL table and Record count of DISTRIBUTION_SL Table.


The update statement should be like below


UPDATE DISTRIBUTION_SL
Set recipnm = datarecipient1name,emailadr = datarecipient1email where requestid = 123456 and recordnumber = 1,
recipnm = datarecipient2name,emailadr = datarecipient2email where requestid = 123456 and recordnumber = 2,
recipnm = datarecipient2name,emailadr = datarecipient2email where requestid = 123456 and recordnumber = 3,
recipnm = datarecipient2name,emailadr = datarecipient2email where requestid = 123456 and recordnumber = 4,
recipnm = datarecipient2name,emailadr = datarecipient2email where requestid = 123456 and recordnumber = 5,
recipnm = datarecipient2name,emailadr = datarecipient2email where requestid = 123456 and recordnumber = 6,
recipnm = datarecipient2name,emailadr = datarecipient2email where requestid = 123456 and recordnumber = 7,
recipnm = datarecipient2name,emailadr = datarecipient2email where requestid = 123456 and recordnumber = 8,
recipnm = datarecipient2name,emailadr = datarecipient2email where requestid = 123456 and recordnumber = 9,
where requestid = 123456


Insert Statement should be like below


Insert into DISTRIBUTION_SL values(123456,datarecipient6name,datarecipient6email,new sequence number,1234567890)


Is this something doable

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/10/2013 :  14:37:14  Show Profile  Reply with Quote
i Think this is what you're after

INSERT [dbo].[DISTRIBUTION_SL]
SELECT sm.[requestid],sm.[rptdis],t.*,(SELECT MAX(seq) FROM [dbo].[DISTRIBUTION_SL] WHERE rptdis = sm.rptdis)+1,'1234567890'
FROM [dbo].[STOPLOSS_MAINTENANCE] sm
CROSS APPLY (VALUES([DATARECIPIENT1NAME],[DATARECIPIENT1EMAIL]),
([DATARECIPIENT2NAME],[DATARECIPIENT2EMAIL]),
([DATARECIPIENT3NAME],[DATARECIPIENT3EMAIL]),
([DATARECIPIENT4NAME],[DATARECIPIENT4EMAIL]),
([DATARECIPIENT5NAME],[DATARECIPIENT5EMAIL]),
([DATARECIPIENT6NAME],[DATARECIPIENT6EMAIL]),
([DATARECIPIENT7NAME],[DATARECIPIENT7EMAIL]),
([DATARECIPIENT8NAME],[DATARECIPIENT8EMAIL]),
([DATARECIPIENT9NAME],[DATARECIPIENT9EMAIL]))t(recipnm,emailadr)
WHERE recipnm IS NOT NULL
AND emailadr IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM DISTRIBUTION_SL
WHERE requestid = sm.[requestid]
AND rptdis= sm.[rptdis]
AND recipnm = t.recipnm
and emailadr = t.emailadr
)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 10/10/2013 :  15:21:10  Show Profile  Reply with Quote
It will take some time for me to even understand what you gave me but i wanted to say that iam getting a syntax error

also how do i use this for a single request

thanks
quote:
Originally posted by visakh16

i Think this is what you're after

INSERT [dbo].[DISTRIBUTION_SL]
SELECT sm.[requestid],sm.[rptdis],t.*,(SELECT MAX(seq) FROM [dbo].[DISTRIBUTION_SL] WHERE rptdis = sm.rptdis)+1,'1234567890'
FROM [dbo].[STOPLOSS_MAINTENANCE] sm
CROSS APPLY (VALUES([DATARECIPIENT1NAME],[DATARECIPIENT1EMAIL]),
([DATARECIPIENT2NAME],[DATARECIPIENT2EMAIL]),
([DATARECIPIENT3NAME],[DATARECIPIENT3EMAIL]),
([DATARECIPIENT4NAME],[DATARECIPIENT4EMAIL]),
([DATARECIPIENT5NAME],[DATARECIPIENT5EMAIL]),
([DATARECIPIENT6NAME],[DATARECIPIENT6EMAIL]),
([DATARECIPIENT7NAME],[DATARECIPIENT7EMAIL]),
([DATARECIPIENT8NAME],[DATARECIPIENT8EMAIL]),
([DATARECIPIENT9NAME],[DATARECIPIENT9EMAIL]))t(recipnm,emailadr)
WHERE recipnm IS NOT NULL
AND emailadr IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM DISTRIBUTION_SL
WHERE requestid = sm.[requestid]
AND rptdis= sm.[rptdis]
AND recipnm = t.recipnm
and emailadr = t.emailadr
)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2013 :  02:33:52  Show Profile  Reply with Quote
whats the sql version you're using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2013 :  02:55:08  Show Profile  Reply with Quote
See the full illustration using your sample data here


declare @t table
(
requestid int,
recipnm varchar(50),
emailadr  varchar(100),
seq int,
phone varchar(100)
)
insert @t
values
(313524,'Jennifer XYZ','jpool@yahoo.com',4,'111-111-1111'),
(313524,'Lisa Yaris','lisa.y@gmail.com',	 5,'222-222-2222'),
(313524,'Heather Matiz','heather_m@hotmail.com',	2,'333-333-3333'),
(313524,'Paul Sentra','paul@aol.com',	 1,'444-444-4444'),
(313524,'Torin xavier','elcl@abc.com',	 3,'555-555-5555')

declare @u table
(
[requestid] [numeric](18, 0) NOT NULL,
[rptdis] [char](1) NULL,
[DATARECIPIENT1NAME] [varchar](50) NULL,
[DATARECIPIENT1EMAIL] [varchar](100) NULL,
[DATARECIPIENT2NAME] [varchar](50) NULL,
[DATARECIPIENT2EMAIL] [varchar](100) NULL,
[DATARECIPIENT3NAME] [varchar](50) NULL,
[DATARECIPIENT3EMAIL] [varchar](100) NULL,
[DATARECIPIENT4NAME] [varchar](50) NULL,
[DATARECIPIENT4EMAIL] [varchar](100) NULL,
[DATARECIPIENT5NAME] [varchar](50) NULL,
[DATARECIPIENT5EMAIL] [varchar](100) NULL,
[DATARECIPIENT6NAME] [varchar](50) NULL,
[DATARECIPIENT6EMAIL] [varchar](100) NULL,
[DATARECIPIENT7NAME] [varchar](50) NULL,
[DATARECIPIENT7EMAIL] [varchar](100) NULL,
[DATARECIPIENT8NAME] [varchar](50) NULL,
[DATARECIPIENT8EMAIL] [varchar](100) NULL,
[DATARECIPIENT9NAME] [varchar](50) NULL,
[DATARECIPIENT9EMAIL] [varchar](100) NULL,
[CURRENTINDICATOR] [char](1) NULL
)

insert @u 
values (313524,	'B','Jennifer XYZ','jpool@yahoo.com','Lisa Yaris','lisa.y@gmail.com','Heather Matiz','heather_m@hotmail.com','Paul Sentra','paul@aol.com','Torin xavier','elcl@abc.com','Merrylou Schlarb','Merrylou.schlarb@symetra.com',NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	'Y')


INSERT @t
SELECT sm.[requestid],t.*,(SELECT MAX(seq) FROM @t WHERE rptdis = sm.rptdis)+1,'1234567890'
FROM @u sm
CROSS APPLY (VALUES([DATARECIPIENT1NAME],[DATARECIPIENT1EMAIL]),
([DATARECIPIENT2NAME],[DATARECIPIENT2EMAIL]),
([DATARECIPIENT3NAME],[DATARECIPIENT3EMAIL]),
([DATARECIPIENT4NAME],[DATARECIPIENT4EMAIL]),
([DATARECIPIENT5NAME],[DATARECIPIENT5EMAIL]),
([DATARECIPIENT6NAME],[DATARECIPIENT6EMAIL]),
([DATARECIPIENT7NAME],[DATARECIPIENT7EMAIL]),
([DATARECIPIENT8NAME],[DATARECIPIENT8EMAIL]),
([DATARECIPIENT9NAME],[DATARECIPIENT9EMAIL]))t(recipnm,emailadr)
WHERE recipnm IS NOT NULL
AND emailadr IS NOT NULL
AND [CURRENTINDICATOR]= 'Y'

AND NOT EXISTS (SELECT 1 FROM @t
WHERE requestid = sm.[requestid]
AND recipnm = t.recipnm
and emailadr = t.emailadr
)

SELECT * FROm @t



output
---------------------------------------------
requestid	recipnm	emailadr	seq	phone
---------------------------------------------------------------------
313524	Jennifer XYZ	jpool@yahoo.com	4	111-111-1111
313524	Lisa Yaris	lisa.y@gmail.com	5	222-222-2222
313524	Heather Matiz	heather_m@hotmail.com	2	333-333-3333
313524	Paul Sentra	paul@aol.com	1	444-444-4444
313524	Torin xavier	elcl@abc.com	3	555-555-5555

313524	Merrylou Schlarb	Merrylou.schlarb@symetra.com	6	1234567890




The record in blue shows the inserted value

I've included only required columns in above illustration

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 10/11/2013 :  09:04:25  Show Profile  Reply with Quote
My SQL version is 10.0.2531..0 .The error is

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.

Thanks
quote:
Originally posted by visakh16

whats the sql version you're using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Edited by - jim_jim on 10/11/2013 09:24:23
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2013 :  13:49:21  Show Profile  Reply with Quote
what does this return?

EXEC sp_dbcmptlevel 'your database name'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

USA
306 Posts

Posted - 10/11/2013 :  15:50:38  Show Profile  Reply with Quote
The current compatibility level is 90.

quote:
Originally posted by visakh16

what does this return?

EXEC sp_dbcmptlevel 'your database name'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/12/2013 :  04:01:24  Show Profile  Reply with Quote
VALUES clause will work only from compatibility level 100 onwards

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/12/2013 :  04:07:55  Show Profile  Reply with Quote
Here's a solution which works for your version


declare @t table
(
requestid int,
recipnm varchar(50),
emailadr  varchar(100),
seq int,
phone varchar(100)
)
insert @t
values
(313524,'Jennifer XYZ','jpool@yahoo.com',4,'111-111-1111'),
(313524,'Lisa Yaris','lisa.y@gmail.com',	 5,'222-222-2222'),
(313524,'Heather Matiz','heather_m@hotmail.com',	2,'333-333-3333'),
(313524,'Paul Sentra','paul@aol.com',	 1,'444-444-4444'),
(313524,'Torin xavier','elcl@abc.com',	 3,'555-555-5555')

declare @u table
(
[requestid] [numeric](18, 0) NOT NULL,
[rptdis] [char](1) NULL,
[DATARECIPIENT1NAME] [varchar](50) NULL,
[DATARECIPIENT1EMAIL] [varchar](100) NULL,
[DATARECIPIENT2NAME] [varchar](50) NULL,
[DATARECIPIENT2EMAIL] [varchar](100) NULL,
[DATARECIPIENT3NAME] [varchar](50) NULL,
[DATARECIPIENT3EMAIL] [varchar](100) NULL,
[DATARECIPIENT4NAME] [varchar](50) NULL,
[DATARECIPIENT4EMAIL] [varchar](100) NULL,
[DATARECIPIENT5NAME] [varchar](50) NULL,
[DATARECIPIENT5EMAIL] [varchar](100) NULL,
[DATARECIPIENT6NAME] [varchar](50) NULL,
[DATARECIPIENT6EMAIL] [varchar](100) NULL,
[DATARECIPIENT7NAME] [varchar](50) NULL,
[DATARECIPIENT7EMAIL] [varchar](100) NULL,
[DATARECIPIENT8NAME] [varchar](50) NULL,
[DATARECIPIENT8EMAIL] [varchar](100) NULL,
[DATARECIPIENT9NAME] [varchar](50) NULL,
[DATARECIPIENT9EMAIL] [varchar](100) NULL,
[CURRENTINDICATOR] [char](1) NULL
)

insert @u 
values (313524,	'B','Jennifer XYZ','jpool@yahoo.com','Lisa Yaris','lisa.y@gmail.com','Heather Matiz','heather_m@hotmail.com','Paul Sentra','paul@aol.com','Torin xavier','elcl@abc.com','Merrylou Schlarb','Merrylou.schlarb@symetra.com',NULL,	NULL,	NULL,	NULL,	NULL,	NULL,	'Y')




INSERT @t
SELECT sm.[requestid],recipnm,emailadr,(SELECT MAX(seq) FROM @t WHERE rptdis = sm.rptdis)+1,'1234567890'
FROM @u sm
INNER JOIN (
SELECT requestid,[DATARECIPIENT1NAME],[DATARECIPIENT1EMAIL] FROM @u UNION ALL
SELECT requestid,[DATARECIPIENT2NAME],[DATARECIPIENT2EMAIL] FROM @u UNION ALL
SELECT requestid,[DATARECIPIENT3NAME],[DATARECIPIENT3EMAIL] FROM @u UNION ALL
SELECT requestid,[DATARECIPIENT4NAME],[DATARECIPIENT4EMAIL] FROM @u UNION ALL
SELECT requestid,[DATARECIPIENT5NAME],[DATARECIPIENT5EMAIL] FROM @u UNION ALL
SELECT requestid,[DATARECIPIENT6NAME],[DATARECIPIENT6EMAIL] FROM @u UNION ALL
SELECT requestid,[DATARECIPIENT7NAME],[DATARECIPIENT7EMAIL] FROM @u UNION ALL
SELECT requestid,[DATARECIPIENT8NAME],[DATARECIPIENT8EMAIL] FROM @u UNION ALL
SELECT requestid,[DATARECIPIENT9NAME],[DATARECIPIENT9EMAIL] FROM @u)t(requestid,recipnm,emailadr)
ON t.requestid = sm.requestid
WHERE t.recipnm IS NOT NULL
AND t.emailadr IS NOT NULL
AND sm.[CURRENTINDICATOR]= 'Y'

AND NOT EXISTS (SELECT 1 FROM @t
WHERE requestid = sm.[requestid]
AND recipnm = t.recipnm
and emailadr = t.emailadr
)

SELECT * FROm @t


output
--------------------------------------------------------------
requestid	recipnm	emailadr	seq	phone
--------------------------------------------------------------
313524	Jennifer XYZ	jpool@yahoo.com	4	111-111-1111
313524	Lisa Yaris	lisa.y@gmail.com	5	222-222-2222
313524	Heather Matiz	heather_m@hotmail.com	2	333-333-3333
313524	Paul Sentra	paul@aol.com	1	444-444-4444
313524	Torin xavier	elcl@abc.com	3	555-555-5555
313524	Merrylou Schlarb	Merrylou.schlarb@symetra.com	6	1234567890



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000