| Author |
 Topic  |
|
|
almontejr
Starting Member
5 Posts |
 Posted - 06/10/2012 : 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
India
227 Posts |
Posted - 06/11/2012 : 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 - 06/11/2012 : 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 - 06/11/2012 : 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
India
227 Posts |
Posted - 06/11/2012 : 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 - 06/11/2012 : 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
India
227 Posts |
Posted - 06/12/2012 : 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" |
|
|
| |
Topic |
|
|
|
Updating membership expiration date
