June 11, 2012 at 3:37 am
Hello ! , i was wondering if you could answer a question for me , I have a membership database with thousands of records 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
After the query is executed this is how it should look (all records now have same expiration date)
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
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!
June 11, 2012 at 4:49 am
You will need to provide the table schema and provide insert scripts with sample data for anybody to help you with the script.
=======================================================================================
Visit my technical reference; you might find some of your issues already documented.
June 11, 2012 at 4:54 am
A self join will do the trick
declare @tab table (name nvarchar(100), MemberID INT, PrimaryID INT, Expiration DATE)
insert into @tab values
('John Smith',1024,null,'2013-01-01'),
('Mary Smith',1025,1024,'2013-01-15'),
('Jim Smith',2033,1024,'2013-01-18'),
('Kate Smith',2100,1024,'2013-01-17')
select * from @tab
UPDATE t
SET Expiration = sub.Expiration
FROM @tab t
INNER JOIN @tab sub
ON
t.PrimaryID = sub.MemberID
WHERE
sub.PrimaryID is null
select * from @tab
June 11, 2012 at 5:07 am
I gave you this solution in your other thread as well.
--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
June 11, 2012 at 1:11 pm
Thank you for the help , the problem i ran into with your solution is that i have more than 1 person who is the primary so i basically get this error back
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.
this is a better example of the data i encounter
Name MemberID PrimaryID Expiration
Mary Smith1025 1024 2013-01-15
Jim Smith 2033 1024 2013-01-18
Kate smith2100 1024 2013-01-17
Kate smith2100 0 2013-01-17
Juan Almonte8836 1999 2013-01-15
Simeon Almonte3432 1999 2013-01-18
Pedro Almonte1999 0 2013-01-17
Jacob Almonte8978 1999 2013-02-25
as you can see in this sample i have 2 families and each family is linked to the primary by the fact that they have his Member ID as their Primary ID.
June 11, 2012 at 1:13 pm
This is basically the schema , im only interestead in updating the Expiration date based on the primary member ( pass holder)
/****** Object: Table [dbo].[Passes] Script Date: 6/11/2012 12:12:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Passes](
[PassNo] [int] NOT NULL,
[PassAcct] [char](24) NOT NULL,
[First] [char](30) NULL,
[Middle] [varchar](30) NULL,
[Last] [char](30) NULL,
[Street1] [char](30) NULL,
[Street2] [char](30) NULL,
[City] [varchar](40) NULL,
[State] [varchar](20) NULL,
[Zip] [char](10) NULL,
[CountryCode] [char](2) NULL,
[Phone] [char](16) NULL,
[Email] [varchar](128) NULL,
[DateOpened] [datetime] NULL,
[ValidDays] [int] NULL,
[Kind] [int] NULL,
[ValidUntil] [datetime] NULL,
[DOB] [datetime] NULL,
[DateUsed] [datetime] NULL,
[Master] [int] NULL,
[Purchaser] [int] NULL,
[MaxParty] [int] NULL,
[Price] [money] NULL,
[Tax] [money] NULL,
[UseCount] [int] NULL,
[ReissueCount] [int] NULL,
[Status] [int] NULL,
[User01] [varchar](255) NULL,
[User02] [varchar](255) NULL,
[User03] [varchar](255) NULL,
[User04] [varchar](255) NULL,
[User05] [varchar](255) NULL,
[User06] [varchar](255) NULL,
[User07] [varchar](255) NULL,
[User08] [varchar](255) NULL,
[User09] [varchar](255) NULL,
[User10] [varchar](255) NULL,
[LimitCount] [int] NULL,
[Company] [int] NULL,
[Category] [int] NULL,
[SubCat] [int] NULL,
[TaxFlags] [char](8) NULL,
[ProdNo] [int] NULL,
[FKey] [int] NULL,
[ValueKind] [int] NULL,
[Value] [money] NULL,
[AccessCode] [int] NULL,
[VisualID] [varchar](40) NULL,
[FKeyFlags] [char](8) NULL,
[FKeyKind] [int] NULL,
[PriorPassAcct] [char](24) NULL,
[PriorPassKind] [int] NULL,
[NoteID] [int] NULL,
[PictureID] [int] NULL,
[DiscountID] [int] NULL,
[PLU] [char](20) NULL,
[MaxUses] [int] NULL,
[TaxMethods] [char](8) NULL,
[NodeNo] [int] NULL,
[TransNo] [int] NULL,
[RecordVersion] [int] NOT NULL,
[LastUpdate] [datetime] NOT NULL,
[LastUpdatedBy] [varchar](20) NULL,
[ContactID] [int] NULL,
[AdultQty] [int] NULL,
[ChildQty] [int] NULL,
[ValidFrom] [datetime] NULL,
[PendingPictureID] [int] NULL,
[CustomerID] [int] NULL,
[GalaxySiteID] [int] NULL,
CONSTRAINT [PKPassesPassNo] PRIMARY KEY CLUSTERED
(
[PassNo] 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
SET ANSI_PADDING OFF
GO
June 11, 2012 at 11:12 pm
Your Sample Data seems inconsistent. Why are there two entries of the same person(Kate Smith) in your table, which completely changes the relationship of the PrimaryId on the MemberId completely for any logic to be applied on it. You need to take care of such Data Inconsistencies before you move on to do something else.
I have written a query with a Self JOIN which would only work if the the Data is consistent. I have changed the Sample Data a bit to show you how it works. Maybe if you can't change the Data then you might be able to manufacture something like the following query to make it work for your data:
--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
Hope this helps.
June 12, 2012 at 12:32 am
Vinu , thank you for all your help, just to clarify , the mary smith record was a typo (mistake) when i was cutting and pasting , basically i have thousand of records and every family has 1 primary , the primary's member id is used in the rest of the familie's record in the primary column. i will test your last suggestion and let you know. Thank you
June 12, 2012 at 1:51 am
That Worked great!!! , thank you very much !!! I was able to update 130,000 records in 1 pass!!
June 12, 2012 at 3:14 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply