|
|
Posted by pbd22 on 11/08/06 17:20
Hi.
I was using abbreviated names for the table and the columns for
security
purposes, but, this is the actual table. so, dont be confused by the
new
nomenclature (add is addlist and add_s is add_status)
CREATE TABLE [AddList] (
[add_id] [int] IDENTITY (1, 1) NOT NULL ,
[add_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[add_date] [datetime] NULL ,
[email_address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[add_status] [bit] NULL ,
CONSTRAINT [PK__AddList__00DF2177] PRIMARY KEY CLUSTERED
(
[add_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
HERE IS THE EDITPROFILE TABLE WHICH HAS THE PUBLIC_NAME COLUMN:
CREATE TABLE [EditProfile] (
[edit_id] [int] IDENTITY (1, 1) NOT NULL ,
[headline] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[about_me] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[edit_date] [datetime] NULL ,
[email_address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[public_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[interests] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK__EditProfile__58D1301D] PRIMARY KEY CLUSTERED
(
[edit_id]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Here Is The Results of A Select * From AddList statement (add addresses
are fake):
(col headers adjusted for formatting)
add_id add_email add_date email_address add_status
1 black@hogwarts.edu 2006-11-03 12:08:57.450 granger@hogwarts.edu 0
14 mags23@heaven.net 2006-11-03 16:17:54.513 black@hogwarts.edu 0
15 freud@heaven.net 2006-11-03 16:27:16.810 black@hogwarts.edu 0
22 franklin@heaven.net 2006-11-03 21:18:38.560 black@hogwarts.edu 0
23 invisible@heaven.net 2006-11-04 09:51:30.293 yodi@hogwarts.edu 0
24 gstein@hogwarts.edu 2006-11-04 09:52:21.937 yodi@hogwarts.edu 0
25 jlee@heaven.net 2006-11-04 10:32:52.310 yodi@hogwarts.edu 0
26 golds@heaven.net 2006-11-04 10:37:21.293 yodi@hogwarts.edu 0
27 black@hogwarts.edu 2006-11-04 10:40:57.670 yodi@hogwarts.edu 0
28 snape@hogwarts.edu 2006-11-04 10:44:34.827 yodi@hogwarts.edu 0
29 granger@hogwarts.edu 2006-11-04 16:22:04.077 yodi@hogwarts.edu 0
30 freud@heaven.net 2006-11-04 16:28:38.623 yodi@hogwarts.edu 0
31 pria@hogwarts.edu 2006-11-04 16:43:12.043 yodi@hogwarts.edu 0
32 yodi@hogwarts.edu 2006-11-04 16:45:54.280 yodi@hogwarts.edu 0
33 black@hogwarts.edu 2006-11-05 22:01:34.327 mags23@heaven.net 0
35 black@hogwarts.edu 2006-11-05 22:02:10.233 potter@hogwarts.edu 0
36 black@hogwarts.edu 2006-11-05 22:02:15.700 dumbledore@hogwarts.edu 0
37 black@hogwarts.edu 2006-11-05 23:35:27.560 black@hogwarts.edu 0
38 potter@hogwarts.edu 2006-11-05 23:36:12.983 black@hogwarts.edu 0
39 mags23@heaven.net 2006-11-06 14:04:19.983 dumbledore@hogwarts.edu 0
40 mags23@heaven.net 2006-11-06 14:11:28.373 pria@hogwarts.edu 0
AND, AGAIN, HERE IS THE UPDATE STATEMENT:
UPDATE addlist
SET add_status = 0
WHERE add_status = 1
AND add_status IN (
SELECT a.add_status
FROM addlist a, editprofile e
WHERE a.email_address = e.email_address
and e.public_name = 'Wolfie' and a.add_email = 'mags23@heaven.net'
)
the above statement turns a row of "1"s to a row of "0"s . Same happens
with
the OR statement.
Navigation:
[Reply to this message]
|