Posted by blvandeb@yahoo.com on 11/08/06 17:25

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'
> )

[Back to original message]