Update and Insert statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update and Insert statement

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-10-10 : 12:10:48

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

52326 Posts

Posted - 2013-10-10 : 14:37:14

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

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-10-10 : 15:21:10

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 02:33:52

whats the sql version you're using?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 02:55:08

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

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-10-11 : 09:04:25

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 13:49:21

what does this return?

EXEC sp_dbcmptlevel 'your database name'

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

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-10-11 : 15:50:38

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-12 : 04:01:24

VALUES clause will work only from compatibility level 100 onwards

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-12 : 04:07:55

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources