Updating membership expiration date

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

Updating membership expiration date

Author

Topic

almontejr
Starting Member

5 Posts

Posted - 2012-06-10 : 20:37:13

Hello ! , i was wondering if you could answer a question for me , I have a membership database where I need to update the expiration date of all of the family based on the head of household's expiration date.

This is the sample data :

Name.              MemberID    PrimaryID         Expiration

John Smith.      1024.                Null.               01-01-2013
Mary Smith.       1025.               1024.             01-15-2013
Jim Smith.          2033.               1024.             01-18-2013
Kate smith          2100.                1024.             01-17-2013

What I basically want to do is I want to update the expiration of the rest of the family by using the expiration date from the primary (John smith record.) What make John the primary is that the primary column has a 0 while the rest of the family has Johns member ID (1024) . I have a couple of thousand records that I need to update but was wondering if there was an Update statement and would accomplish that in one pass.

Thank you in advance!

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-11 : 03:31:18

Is this what you are looking for?


--Creating Table

Create Table Ex
(Name varchar(20),
 MemberID int,
 PrimaryID int,
 Expiration Date )


--Inserting Sample Data

Insert Into Ex
Select 'Mary Smith',       1025,               1024,             '01-15-2013'
Union ALL
Select 'Jim Smith',        2033,               1024,             '01-18-2013'
Union ALL
Select 'Kate smith',       2100,               1024,             '01-17-2013'


--Query For Your Requirement

Update Ex
Set Expiration = (Select Expiration From Ex Where PrimaryID Is NULL)
Where PrimaryID IS NOT NULL

N 28° 33' 11.93148"
E 77° 14' 33.66384"

almontejr
Starting Member

5 Posts

Posted - 2012-06-11 : 05:24:57

Im not sure i understand your query but this is what the end result should be

Name. MemberID PrimaryID Expiration

John Smith. 1024. Null. 01-01-2013
Mary Smith. 1025. 1024. 01-01-2013
Jim Smith. 2033. 1024. 01-01-2013
Kate smith 2100. 1024. 01-01-2013

so basically all records in the membership database should be updated with the Primary accounts Experation date.

almontejr
Starting Member

5 Posts

Posted - 2012-06-11 : 05:26:31

Also Just want to clarify , im not creating a new table , im simply updating the expiration dates.

quote:
Originally posted by almontejr

Im not sure i understand your query but this is what the end result should be

Name. MemberID PrimaryID Expiration

John Smith. 1024. Null. 01-01-2013
Mary Smith. 1025. 1024. 01-01-2013
Jim Smith. 2033. 1024. 01-01-2013
Kate smith 2100. 1024. 01-01-2013

so basically all records in the membership database should be updated with the Primary accounts Experation date.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-11 : 06:31:49

quote:
Originally posted by almontejr

Im not sure i understand your query but this is what the end result should be

Name. MemberID PrimaryID Expiration

John Smith. 1024. Null. 01-01-2013
Mary Smith. 1025. 1024. 01-01-2013
Jim Smith. 2033. 1024. 01-01-2013
Kate smith 2100. 1024. 01-01-2013

so basically all records in the membership database should be updated with the Primary accounts Experation date.

Did you try the query??....
It will give you the results that you want.
It will not create a table. It will only update the Expiration Date columns for the Rows where Primary Id is not NULL.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

almontejr
Starting Member

5 Posts

Posted - 2012-06-11 : 15:02:17

yes I did try it , if i do it for one set of records it work fine , but if i run it against a database that contaies many records then i get.

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have thousands of records that i need to update , so there will be more than 1 primary.

quote:
Originally posted by vinu.vijayan

quote:
Originally posted by almontejr

Im not sure i understand your query but this is what the end result should be

Name. MemberID PrimaryID Expiration

John Smith. 1024. Null. 01-01-2013
Mary Smith. 1025. 1024. 01-01-2013
Jim Smith. 2033. 1024. 01-01-2013
Kate smith 2100. 1024. 01-01-2013

so basically all records in the membership database should be updated with the Primary accounts Experation date.

Did you try the query??....
It will give you the results that you want.
It will not create a table. It will only update the Expiration Date columns for the Rows where Primary Id is not NULL.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-12 : 06:33:59

This got solved using the following query...right??


--Creating Table

Create Table Ex
(Name varchar(20),
 MemberID int,
 PrimaryID int,
 Expiration Date )
 

--Inserting Sample Data(Changed it a bit) 

insert into Ex values 
('Mary Smith',		1025,	 2100,	 '2013-01-15'),
('Jim Smith',		2033,	 2100,	 '2013-01-18'),
('Kate smith',		2100,	 0,		 '2013-01-16'),			--Changed Date For better Understanding Of Query
('Juan Almonte',	8836,	 1999,	 '2013-01-15'),
('Simeon Almonte',	3432,	 1999,	 '2013-01-18'),
('Pedro Almonte',	1999,	 0,		 '2013-01-17'),
('Jacob Almonte',	8978,	 1999,	 '2013-02-25')


--Query For Your Requirement

Update b Set Expiration = a.Expiration From Ex As a
JOIN Ex As b ON a.MemberID = b.PrimaryID

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources