December 12, 2011 at 12:03 pm
select [time]
from citizenservice
group by ctznid,[time] having count([time])=1
i want to update this [time] and set as '' but how can update only these record in the original table. But I have to update only this non duplicate record of every ctznid
December 12, 2011 at 12:53 pm
Your post is a little confusing...
I believe if you got the citizenID as a distinct value you could use that as a subquery for updating...
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
December 12, 2011 at 12:55 pm
Welcome to SSC. You need to provide some more details before anybody can post a complete answer to your question. Start by posting ddl (create table scripts), sample data (insert statements), desired results based on sample data.
I did my best at what MIGHT be your table structure.
create table #CitizenService
(
ctznid int identity not null,
myTime datetime not null
)
Notice in the above I made myTime as a datetime column and used a name that is not a reserved word. It is generally considered much better to have a little bit of explanation in the field title so you aren't guessing. With simply "time" nobody knows what that means. Is that the time the row was inserted? Maybe when they applied for citizenship?
Now for some sample data.
insert #CitizenService
select GETDATE()
union all
select GETDATE()
union all
select GETDATE() - 5
Now what exactly are you trying to do?
i want to update this [time] and set as '' but how can update only these record in the original table. But I have to update only this non duplicate record of every ctznid
Is your "time" column a datetime or a varchar??? '' means drastically different things on those datatypes. Given the above 3 rows which one(s) do you want updated?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2011 at 4:08 pm
OK let me rephrase it , I have made a scenario pleas go through it
Table citzens
Ctznid PKName
C1 A
C2 B
C3 C
Table citzenservice
Citizenserviceid PKCtznid FKServiceTIMEOperator
CS1 C1 S1 08:00 OP1
CS2 C2 S2 08:15 OP1
CS3 C2 S2 08:15 OP2
CS4 C2 S1 08:00 OP2
CS5 C3 S2 OP2
HERE is the scenario,
1.every citizen has some particular services which have to supplied on a particular time or there is no time limitation on some services. and some operators do this services for citizens
2.some services needs two operators on a particular time
3. all fields are varchar(50)
what I need is, to update time to ‘ ’, against all those services which don’t have two operators
Note : Operator Field is to just for understanding of scenario it is not part of the table
Now a SQL group by Select query is
select [time]
from citizenservice
where [time]!='' group by ctznid,[time] having count([time])=1
which returns
Ctznid TIMEit contains following ctizenserviceid outside select area
C1 08:00 CS1
C2 08:00 CS4
I want to replace these two time with ''
and The solution should be like
Citizenserviceid PKCtznid FKServiceTIMEOperator
CS1 C1 S1 OP1
CS2 C2 S2 08:15 OP1
CS3 C2 S2 08:15 OP2
CS4 C2 S1 OP2
CS5 C3 S2 OP2
December 13, 2011 at 7:11 am
If you want actual help you have to post actual information. As I said before...
Start by posting ddl (create table scripts), sample data (insert statements), desired results based on sample data.
I can't write sql when there is no table. I can't test the sql I write when there is no data. I could probably spend my time creating your tables and data but since the service on here is free and I work, I don't feel like volunteering that much of my time today.
If you can post the information and requirements in a consumable format this should be pretty simple.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 8:29 am
First, I want to mention that there are several issues that I see. It looks like you have represented times using strings. If you are measuring durations, you should use a numeric datatype, otherwise you should be using datetime datatypes.
Second, I dislike deleting information. You want to be very sure that you really want to overwrite this information before you proceed with the update, because it's a real pain to recover it.
The key is to use a windowed aggregate to include both detail and aggregate information in the same row. From there, it should be easy to write the appropriate update statement. Unfortunately, windowed aggregates cannot be used in WHERE clauses, so you'll probably need to use a CTE.
SELECT *
,CASE WHEN COUNT([Time]) OVER(PARTITION BY CtznID, [Time]) = 1
THEN NULL
ELSE [Time]
END AS NewTime
FROM CitizenService
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply