March 7, 2014 at 3:31 am
Hi everyone
Hope somebody can help...
I have a table of records that contains 2 records per customer, they both have the same customer id field but their other fields differ. I would like to update both records so that they are exactly the same except for the field in the table called Type which currently shows Archive or Recent - I would like these fields to remain the same but every other field to be changed so they are now the same in both instances. Is there an easy way of doing this?
Thanks in advance.
BO
March 7, 2014 at 3:38 am
Can we assume where 2 records exist for a customer we take the 'Recent' type to update the 'Archive' type when different? Is there only even one recent type for a customer?
Also, some sample data, expected output and table DDL would be handy 😀
March 7, 2014 at 3:55 am
Hi
Yes, the recent 'type' would be used as the basis to update the archived record...
March 7, 2014 at 4:05 am
Rough and ready. Customers matched on customer ID and where have Type 'Archive' will be updated with details from 'Recent' This also assumes that only one 'Recent' entry exists per customer.
DECLARE @Customers TABLE
(
Customerid BIGINT ,
CustomerName NVARCHAR(100) ,
CustomerDetails NVARCHAR(100) ,
CustomerType NVARCHAR(10)
)
INSERT INTO @Customers
( Customerid, CustomerName, CustomerDetails, CustomerType )
VALUES ( 1, N'Teletubbies', N'We rule', N'Archive' ),
( 1, N'Teletubbies', N'Lala', N'Recent' ),
( 2, N'Shaun The Sheep', N'Lives In A Field', N'Recent' )
SELECT *
FROM @Customers
UPDATE cArc
SET CustomerDetails = cRecent.CustomerDetails
FROM @Customers cArc
INNER JOIN @Customers cRecent ON cArc.Customerid = cRecent.Customerid
AND cRecent.CustomerType = 'Recent'
WHERE cArc.CustomerType = 'Archive'
SELECT *
FROM @Customers
March 7, 2014 at 4:15 am
I would do this:
Create a backup of your table, because backup is your friend.. 🙂
Select all archive rows into a temp table
Update the rows in the temp table with the information from the original table where the rows are recent
Delete the rows from the original table where they exist in the temp table
Insert the rows from the temp table back into the original table.
There may well be a neater way to do this, which someone will be along with shortly..
March 7, 2014 at 4:19 am
dbalmf (3/7/2014)
I would do this:Create a backup of your table, because backup is your friend.. 🙂
Select all archive rows into a temp table
Update the rows in the temp table with the information from the original table where the rows are recent
Delete the rows from the original table where they exist in the temp table
Insert the rows from the temp table back into the original table.
There may well be a neater way to do this, which someone will be along with shortly..
That seems an awful lot of work for a quick update? Providing this is tested which my above code does and the assumptions stand up, there should be no need to create backups, temp tables etc IMHO. I'm not saying bad idea, I'm just saying I generally would not take that approach
March 7, 2014 at 4:34 am
March 7, 2014 at 4:35 am
dbalmf (3/7/2014)
Hi Michael, your solution is much more elegant than mine.. And quicker.. 🙂
Thanks :blush:
March 7, 2014 at 5:13 am
Thanks everyone, particulalrly Michael, you guys have saved my bacon!!
BO:-D
March 7, 2014 at 6:27 am
ByronOne (3/7/2014)
Thanks everyone, particulalrly Michael, you guys have saved my bacon!!BO:-D
No worries. Glad to have helped
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply