hi.

I am having probelms with an update statement. every time
i run it, "every" row updates, not just the one(s) intended.

so, here is what i have. i have tried this with both AND and OR
and neither seem to work.

i dont know why this is elluding me, but i'd appreciate help with the
solution.

thanks.

UPDATE add
SET add_s = 1
WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'
or add_s in
(
SELECT a.add_s
FROM add a, edit e
WHERE a.email_address = e.email_address
and e.public_name = 'professor'
)

Try adding some brackets

UPDATE add
SET add_s = 1
WHERE (add.add_status = 0 and add.add_email = 'mag...@rice.edu' )
or add_s in
(
SELECT a.add_s
FROM add a, edit e
WHERE a.email_address = e.email_address
and e.public_name = 'professor'
)

thanks for the suggestion,
but the results are still the same - all the rows update
to the new value, not just the single, intended row.

other thoughts?

pbd22 wrote:
> thanks for the suggestion,
> but the results are still the same - all the rows update
> to the new value, not just the single, intended row.
>
> other thoughts?

Test your WHERE clause as follows:

SELECT * FROM add
WHERE (add.add_status = 0 and add.add_email = 'mag...@rice.edu' )
or add_s in
(
SELECT a.add_s
FROM add a, edit e
WHERE a.email_address = e.email_address
and e.public_name = 'professor'
)




-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

yes, thank you.
i have done that and the results are as expected.
the left-hand side of the OR condition evaluates,
so, all values that are 0 and have the mag... email
address are returned. the right-hand side does
not evaluate. if i run the statement with the AND
condition, i get nothing back.

Alex Kuznetsov wrote:
> pbd22 wrote:
> > thanks for the suggestion,
> > but the results are still the same - all the rows update
> > to the new value, not just the single, intended row.
> >
> > other thoughts?
>
> Test your WHERE clause as follows:
>
> SELECT * FROM add
> WHERE (add.add_status = 0 and add.add_email = 'mag...@rice.edu' )
> or add_s in
> (
> SELECT a.add_s
> FROM add a, edit e
> WHERE a.email_address = e.email_address
> and e.public_name = 'professor'
> )
>
>
>
>
> -----------------------
> Alex Kuznetsov
> http://sqlserver-tips.blogspot.com/
> http://sqlserver-puzzles.blogspot.com/

anybody else? this is driving me bonkers.

what i am trying to do is simply change a value of 0 to 1in
all rows where a given email address exists. i have to
use the select in the where clause because i need to join
2 tables (where e.public_name = 'professor').

if you dont have an idea how to acheive this given the code i
have posted, maybe you could do this on your own DB and
show me the code?

any leads much appreciated.

thanks.

pbd22 wrote:
> yes, thank you.
> i have done that and the results are as expected.
> the left-hand side of the OR condition evaluates,
> so, all values that are 0 and have the mag... email
> address are returned. the right-hand side does
> not evaluate. if i run the statement with the AND
> condition, i get nothing back.
>
> Alex Kuznetsov wrote:
> > pbd22 wrote:
> > > thanks for the suggestion,
> > > but the results are still the same - all the rows update
> > > to the new value, not just the single, intended row.
> > >
> > > other thoughts?
> >
> > Test your WHERE clause as follows:
> >
> > SELECT * FROM add
> > WHERE (add.add_status = 0 and add.add_email = 'mag...@rice.edu' )
> > or add_s in
> > (
> > SELECT a.add_s
> > FROM add a, edit e
> > WHERE a.email_address = e.email_address
> > and e.public_name = 'professor'
> > )
> >
> >
> >
> >
> > -----------------------
> > Alex Kuznetsov
> > http://sqlserver-tips.blogspot.com/
> > http://sqlserver-puzzles.blogspot.com/

Please post your table structures, sample data & expected results so that
others can understand your requirements better. For details see:
www.aspfaq.com/5006

--
Anith

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.

a couple of suggestions...I hope they are helpful.

1) Divided the update into two statements and test each individually.

UPDATE add
SET add_s = 1
WHERE (add.add_status = 0 and add.add_email =
'mag...@rice.edu' )

go

update add
set add_s = 1
where add_s in
(
SELECT a.add_s
FROM add a, edit e
WHERE a.email_address = e.email_address
and e.public_name = 'professor'
)
go

2) I think your subquery pulls back an add_S fields and then you check
the add table to see if it has a value in the subquery. Any record with
an add_s field in the table that matches the add_s field from your
subquery will be updated. For example if the add_S field from the
subquery could return 1, 2 or 3 which will then update any record in
the add table with an add_s value of 1, 2, or 3. I think your subquery
should include a record identifier and you should state where record
identifier in subquery. Based on your example, email might work if no
other unique ids are available.

New Suggestion for second query:
update add
set add_S = 1
where recordID in
(select recordID from add a, edit e
where a.email_address = e.email address
and e.public_name = 'professor')

pbd22 wrote:
> hi.
>
> I am having probelms with an update statement. every time
> i run it, "every" row updates, not just the one(s) intended.
>
> so, here is what i have. i have tried this with both AND and OR
> and neither seem to work.
>
> i dont know why this is elluding me, but i'd appreciate help with the
> solution.
>
> thanks.
>
> UPDATE add
> SET add_s = 1
> WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'
> or add_s in
> (
> SELECT a.add_s
> FROM add a, edit e
> WHERE a.email_address = e.email_address
> and e.public_name = 'professor'
> )

EXPECTED RESULTS:

There should "always" be one row that matches the update statement
based on the
public_name column of the EditProfile table, which is unique. This is
an Add User
page.

The user requests an add, which leaves a 0 in the add_status column.
When the
current user clicks on "Add User", the update statement is supposed to
change
add_status from 0 (pending) to 1 (accepted). The select statement in
the where
clause is necessary because we need to know the public_name of the
requesting
user so one unique row is returned.

so, in AddList,

email_address describes the user that did the requesting
add_email describes the user that is acting upon the request (current
user)
add_status describes the status of the request (pending/accepted)

and, in EditProfile,

public_name describes the unique name of the user that did the
requesting

************************************************** ***********************************************
THE INDENDED RESULTS of this code is to change the row with the current
user's email address (add_email) and the requesting user's public name
(public_name)
from 0 to 1.
************************************************** ***********************************************

below is the pseudo code of what i am attempting:

update addlist
set add_status to accepted (1)
where add_status is pending (0)
and do this where the requesting user's public name is 'Wolfie'
(public_name)
and the current user's email address is 'mags23@heaven.net' (add_email)