delete a field help?

I have a user who has submitted a survey twice, and I have to delete the second entry.
But every user has a UserID and the data for the second submission has the same UserID (the only thing different is the fact she submitted data 5 seconds later)as this is one of the unique values used in many tables.
So I have deleted data in other tables but If I try to delete it from this particulat table I can't as I am really trying to delete that ID, so how can I get rid off that record?You don't have a primary key on that field with a unique constraint? (something like an identity column?)

If you don't have anything to uniquely identify the record, you will have to select one of the records into a temp table, delete the record, then insert the record back. There really isn't any other way to do it.|||UserId has an indentity set, but not in the table that it references to,it is not the primary key.

Could you explain a bit more what would I need to do, as I have only done regular delitions,where things go smooth.

I will attach a file with the error eventhough you probabaly know what it is

Thanks for help

I forgot to tell you that the record I am trying to delete it from is in a view,don't konw if that makes any difference?|||Can you post the table structure of the table you're trying to delete from and the table that's not letting you delete?|||ok,viewSurveySubmission is where the double record is and RelocateeServices will not let me delete it.|||You need to find where the problem is in the base tables and delete the record from there. The view will try to delete from both tables, which it cannot do because another record is still using one of the records from a table you are trying to delete from. Make sense?

In this case find the duplicate in the base table and delete that record.|||Thanks to all willing to help,but I have actually been able to do it myself.

Thanks

Defining week ending date.

Hello,
I have a w ending date question. Here is my table (just for demo
purposes)
CREATE TABLE [dbo].[TestTable] (
[userID] [varchar] (10) NULL ,
[u_key] [int] NULL ,
[TS] [datetime] NULL
) ON [PRIMARY]
GO
insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
'// This is the query I run
Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
From testtable a
Group by a.u_key, DATEPART(ww,a.ts)
'// I get this Result set, which is exactly what I want.
u_key Period Counter
2 28 2
3 28 1
2 30 1
4 30 1
2 31 2
2 32 3
I am assuming that using the DatePart(ww..) automagically makes the
wending a Saturday. Now, my client wants to change the w ending to
Thursday (or whatever). I have no idea how I would change the query. I
most definitely need to have the period number returned as part of the
select clause.
Thanks for all your help.
-JackJack,
1. Don't use DATEPART to calculate w number if you want to calculate acco
rding to the ISO
standard (where this w is w 38). SQL Server DATEPART considers this we
ek to be w number 39.
If you want to calculate according to ISO, install the ISOWEEK function whic
h you find in Books
Online.
2. Use SET DATEFIRST con set first day of w. I think ISOWEEK respects thi
s setting, but test just
to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <jack@.jack.net> wrote in message news:9RWXe.39021$Cc5.3100@.lakeread06...ed">
> Hello,
> I have a w ending date question. Here is my table (just for demo purpo
ses)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the wen
ding a Saturday. Now,
> my client wants to change the w ending to Thursday (or whatever). I ha
ve no idea how I would
> change the query. I most definitely need to have the period number return
ed as part of the select
> clause.
> Thanks for all your help.
> -Jack
>|||You could use a calendar table, you'd have to define the ws yourself, but
it gives you complete flexibility (and you only have to do it once).
http://www.aspfaq.com/2519
"Jack" <jack@.jack.net> wrote in message
news:9RWXe.39021$Cc5.3100@.lakeread06...
> Hello,
> I have a w ending date question. Here is my table (just for demo
> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> wending a Saturday. Now, my client wants to change the w ending to
> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>|||The DATEFIRST setting specifies the first day of the w.
SET DATEFIRST sets the first day and @.@.DATEFIRST returns the current setting
So...
SELECT CASE @.@.DATEFIRST
WHEN 1 THEN 7
ELSE @.@.DATEFIRST -1
END AS last_day_of_w
"Jack" wrote:

> Hello,
> I have a w ending date question. Here is my table (just for demo
> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> wending a Saturday. Now, my client wants to change the w ending to
> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>
>