July 24, 2012 at 1:50 am
Hi Everyone,
I have users profiles in a table in my database but some of them have wrong data. i.e, a user can be both male and female, or both adult and teenager, so I would like to remove the wrong values. I imported a new file where the correct gender and agegroup is saved so with 'case when' I tried to replace wrong values in my column. But unfortunately, when both cases return true only the first one gets executed and the other cases are ignored. (i.e., when new gender is 'm' and new age group is '2' and in his profile had 'f,1' the new profile becomes 'm,1') How can I fix that? Thank you in advance.
SELECT
a.username,
REPLACE(
CASE
WHEN ( ( a.gender= 'm') AND ( CHARINDEX(',f,', ISNULL(a.profiles, '')) > 0 )
) THEN REPLACE(a.profiles, f, '')
WHEN ( ( a.gender = 'f' ) AND ( CHARINDEX(',m,', ISNULL(a.profiles, '')) > 0 )
) THEN REPLACE(a.profiles, 'm', '')
WHEN ( ( a.agegroup = '1' ) AND ( CHARINDEX(',2,', ISNULL(a.profiles, '')) > 0 )
) THEN REPLACE(a.profiles, '2', '')
WHEN ( ( a.agegroup = '2' ) AND ( CHARINDEX(',1,', ISNULL(a.profiles, '')) > 0 )
) THEN REPLACE(a.profiles, '1', '')
ELSE a.profile_new
END
,',,',',') AS profiles
July 24, 2012 at 2:04 am
Hi Labrini_hr,
This could do with some sample data and a DDL, but...
I think your problem is you are trying to do oone operation in two steps, whcih isn't going to work. The CASE expression short circuits so once it has a match then that's the result and "it moves on" to the next row.
You need to check for all combinations of the two columns. So somethng like this;
SELECT
a.username,
REPLACE(
CASE
WHEN ( ( a.gender = 'm') AND ( a.agegroup = '1' ) AND ( CHARINDEX(',f,', ISNULL(a.profiles, '')) > 0 )
) THEN REPLACE(a.profiles, f, '')
WHEN ( ( a.gender = 'm' ) AND ( a.agegroup = '2' )AND ( CHARINDEX(',m,', ISNULL(a.profiles, '')) > 0 )
) THEN REPLACE(a.profiles, 'm', '')
WHEN ( ( a.gender = 'f') AND ( a.agegroup = '1' ) AND ( CHARINDEX(',2,', ISNULL(a.profiles, '')) > 0 )
) THEN REPLACE(a.profiles, '2', '')
WHEN ( ( a.gender = 'f') AND ( a.agegroup = '2' ) AND ( CHARINDEX(',1,', ISNULL(a.profiles, '')) > 0 )
) THEN REPLACE(a.profiles, '1', '')
ELSE a.profile_new
END
,',,',',') AS profiles
Rodders...
July 24, 2012 at 2:15 am
Thank you for your answer. Unfortunately, it doesn't work for users that I have to replace both values.
But thank you for your time.
July 24, 2012 at 3:25 am
labri (7/24/2012)
Thank you for your answer. Unfortunately, it doesn't work for users that I have to replace both values.But thank you for your time.
With sample data and DDL someone here will probably post a solution in a matter of minutes.
See the link in my signature for assistance on how to post.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 24, 2012 at 4:50 am
Hi Labri,
Is this any good to you?
SELECT z.username,
REPLACE(
CASE
WHEN z.gender= 'm' AND CHARINDEX(',f,', ISNULL(z.profiles, '')) > 0 THEN REPLACE(z.profiles, f, '')
WHEN z.gender = 'f' AND CHARINDEX(',m,', ISNULL(z.profiles, '')) > 0 THEN REPLACE(z.profiles, 'm', '')
WHEN z.agegroup = '1' AND CHARINDEX(',2,', ISNULL(z.profiles, '')) > 0 THEN REPLACE(z.profiles, '2', '')
WHEN z.agegroup = '2' AND CHARINDEX(',1,', ISNULL(z.profiles, '')) > 0 THEN REPLACE(z.profiles, '1', '')
ELSE z.profile_new
END
,',,',',') AS profiles
FROM
(
SELECT
a.username,
CASE
WHEN a.gender= 'm' AND CHARINDEX(',f,', ISNULL(a.profiles, '')) > 0 THEN REPLACE(a.profiles, f, '')
WHEN a.gender = 'f' AND CHARINDEX(',m,', ISNULL(a.profiles, '')) > 0 THEN REPLACE(a.profiles, 'm', '')
WHEN a.agegroup = '1' AND CHARINDEX(',2,', ISNULL(a.profiles, '')) > 0 THEN REPLACE(a.profiles, '2', '')
WHEN a.agegroup = '2' AND CHARINDEX(',1,', ISNULL(a.profiles, '')) > 0 THEN REPLACE(a.profiles, '1', '')
ELSE a.profile_new
END
) AS profiles
) z
July 24, 2012 at 4:57 am
In fact, I think that can be shortened:
SELECT z.username,
REPLACE(
CASE
WHEN z.agegroup = '1' AND CHARINDEX(',2,', ISNULL(z.profiles, '')) > 0 THEN REPLACE(z.profiles, '2', '')
WHEN z.agegroup = '2' AND CHARINDEX(',1,', ISNULL(z.profiles, '')) > 0 THEN REPLACE(z.profiles, '1', '')
ELSE z.profile_new
END
,',,',',') AS profiles
FROM
(
SELECT
a.username,
CASE
WHEN a.gender= 'm' AND CHARINDEX(',f,', ISNULL(a.profiles, '')) > 0 THEN REPLACE(a.profiles, f, '')
WHEN a.gender = 'f' AND CHARINDEX(',m,', ISNULL(a.profiles, '')) > 0 THEN REPLACE(a.profiles, 'm', '')
ELSE a.profile_new
END
) AS profiles
) z
As Rod said, you're trying to do two operations in one, so I think you'll need to split them out, either into two statements or a nested select like this.
Cheers
Gaz
July 24, 2012 at 6:28 am
Thank all for your time. In order to understand what I am trying to do, you can have a look below:
I have a table 'profiles' in my database and I also have a new table "med_profiles" which has the newest profile data of our users. So I would like to update profile data according to the newer ones. Can anyone help?
--===== Create the test user's profile table with
CREATE TABLE #profiles
( profileID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
username nvarchar(50),
profints nvarchar(max)
)
--==Insert test data
Insert into #profiles(username,profints)
values('name1',',m,1,'),
('name2',',m,2,'),
('name3',',1,')
--check #profile table
--select * from #profiles
---==Create mediation test table which includes the new profiles
CREATE TABLE #med_profiles
( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
username nvarchar(50),
gender nvarchar(50),
agegroup nvarchar(50)
)
----== Insert test data in mediation table
Insert into #med_profiles(username,gender,agegroup)
values('name1','f','2'),
('name2','f','1'),
('name3','f','2')
--check #profile table
--select * from #med_profiles
--My way Trying to update profiles column with new data but unfortunately is not working
select a.username,
REPLACE(
CASE
WHEN ( ( a.gender= 'm') AND ( CHARINDEX(',f,', ISNULL(a.profile_new, '')) > 0 )
) THEN REPLACE(a.profile_new, 'f', '')
WHEN ( ( a.gender = 'f' ) AND ( CHARINDEX(',m,', ISNULL(a.profile_new, '')) > 0 )
) THEN REPLACE(a.profile_new, 'm', '')
WHEN ( ( a.agegroup = '1' ) AND ( CHARINDEX(',2,', ISNULL(a.profile_new, '')) > 0 )
) THEN REPLACE(a.profile_new, '2', '')
WHEN ( ( a.agegroup = '2' ) AND ( CHARINDEX(',1,', ISNULL(a.profile_new, '')) > 0 )
) THEN REPLACE(a.profile_new, '1', '')
ELSE a.profile_new
END
,',,',',') AS profiles
FROM #profiles AS u1 WITH ( NOLOCK )
INNER JOIN (SELECT
u.username,gs.gender,gs.agegroup,
REPLACE(ISNULL(u.profints, ',')
+ CASE WHEN (( gs.Gender !='' ) AND ( gs.Gender IS NOT NULL ))
THEN (gs.Gender)
+ ','
ELSE ''
END
+ CASE
WHEN ( ( gs.agegroup !='' ) AND ( gs.agegroup IS NOT NULL ))
THEN (gs.agegroup) + ','
ELSE ''
END
,',,',',') AS profile_new
FROM #profiles AS u WITH ( NOLOCK )
INNER JOIN #med_profiles as gs WITH ( NOLOCK )
ON u.username = gs.username
WHERE gs.username IS NOT NULL
) a ON u1.username = a.username
------Drop temp tables
--drop table #profiles
--drop table #med_profiles
July 24, 2012 at 7:01 am
Labri,
Thanks for the sample data.
For what you are trying to do, I think you need two updates, you could rewrite as one but I'm pretty sure you would have to update every row. So those that didn't need updating would have to be updated to it's existing value. Which would add to query time.
Unless someone else has an idea? Mine would have use CASE and return either the existing value or the new one for each column of each row.
Based on your test data I've come up with this;
UPDATE #med_profiles
SET gender = p.gender
FROM
(select profileid, username,
SUBSTRING(profints, CHARINDEX(profints, ',', 0) + 2, 1) as 'gender'
from #profiles) p
JOIN #med_profiles m
on p.profileid = m.id
WHERE m.gender <> p.gender
AND PATINDEX('[0-9]', p.gender) = 0
UPDATE #med_profiles
SET agegroup = p.agegroup
FROM
(select profileid, username,
SUBSTRING(REVERSE(profints), CHARINDEX(REVERSE(profints), ',', 0) +2 , 1) as 'agegroup'
from #profiles) p
JOIN #med_profiles m
on p.profileid = m.id
WHERE m.agegroup <> p.agegroup
AND PATINDEX('[0-9]', p.agegroup) = 1
Note because one of your rows in you test data (profints), only has one value in it, the second update uses REVERSE, and both have to compare the result to whether it contains a number.
Question what happens if both columns in a row are correct? Do you still get a correcting row in your "lookup" table. I'm working on the basis it doesn't.
Rodders...
July 24, 2012 at 7:13 am
cascaded (CROSS) APPLY is ideal for this:
DROP TABLE #Sample
CREATE TABLE #Sample (username VARCHAR(20), gender CHAR(1), agegroup VARCHAR(3), profiles VARCHAR(20), profile_new VARCHAR(20))
INSERT INTO #Sample
SELECT username = 'Edward', gender = 'm', agegroup = '1', profiles = ',f,2,', profile_new = 'profile_1' UNION ALL
SELECT 'Victoria', 'f', '2', ',f,1,', 'profile_2'
SELECT
a.username,
a.gender,
a.agegroup,
OldProfile = a.profiles,
a.profile_new,
profiles = CASE WHEN age.profiles <> a.profiles THEN REPLACE(age.profiles,',,',',') ELSE a.profile_new END
FROM #Sample a
CROSS APPLY (
SELECT profiles = CASE
WHEN a.gender = 'm' AND CHARINDEX(',f,', ISNULL(a.profiles, '')) > 0 THEN REPLACE(a.profiles, 'f', '')
WHEN a.gender = 'f' AND CHARINDEX(',m,', ISNULL(a.profiles, '')) > 0 THEN REPLACE(a.profiles, 'm', '')
ELSE a.profiles END
) gender
CROSS APPLY (
SELECT profiles = CASE
WHEN a.agegroup = '1' AND CHARINDEX(',2,', ISNULL(a.profiles, '')) > 0 THEN REPLACE(gender.profiles, '2', '')
WHEN a.agegroup = '2' AND CHARINDEX(',1,', ISNULL(a.profiles, '')) > 0 THEN REPLACE(gender.profiles, '1', '')
ELSE gender.profiles END
) age
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply