Please Help With Complex Update Statement Logic

[pbd22]

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 = 'm*****@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'
)

[markc600]

Try adding some brackets

UPDATE add
SET add_s = 1
WHERE (add.add_status = 0 and add.add_email = '[e-mail]' )
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'
)

[pbd22]

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?

[Alex Kuznetsov]

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 = '[e-mail]' )
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/

[pbd22]

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 = '[e-mail]' )
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/

[pbd22]

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 = '[e-mail]' )
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/

[Anith Sen]

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

[pbd22]

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 bl***@hogwarts.edu 2006-11-03 12:08:57.450 gr*****@hogwarts.edu 0
14 ma****@heaven.net 2006-11-03 16:17:54.513 bl***@hogwarts.edu 0
15 fr***@heaven.net 2006-11-03 16:27:16.810 bl***@hogwarts.edu 0
22 fr******@heaven.net 2006-11-03 21:18:38.560 bl***@hogwarts.edu 0
23 in*******@heaven.net 2006-11-04 09:51:30.293 yo**@hogwarts.edu 0
24 gs****@hogwarts.edu 2006-11-04 09:52:21.937 yo**@hogwarts.edu 0
25 jl**@heaven.net 2006-11-04 10:32:52.310 yo**@hogwarts.edu 0
26 go***@heaven.net 2006-11-04 10:37:21.293 yo**@hogwarts.edu 0
27 bl***@hogwarts.edu 2006-11-04 10:40:57.670 yo**@hogwarts.edu 0
28 sn***@hogwarts.edu 2006-11-04 10:44:34.827 yo**@hogwarts.edu 0
29 gr*****@hogwarts.edu 2006-11-04 16:22:04.077 yo**@hogwarts.edu 0
30 fr***@heaven.net 2006-11-04 16:28:38.623 yo**@hogwarts.edu 0
31 pr**@hogwarts.edu 2006-11-04 16:43:12.043 yo**@hogwarts.edu 0
32 yo**@hogwarts.edu 2006-11-04 16:45:54.280 yo**@hogwarts.edu 0
33 bl***@hogwarts.edu 2006-11-05 22:01:34.327 ma****@heaven.net 0
35 bl***@hogwarts.edu 2006-11-05 22:02:10.233 po****@hogwarts.edu 0
36 bl***@hogwarts.edu 2006-11-05 22:02:15.700 du********@hogwarts.edu 0
37 bl***@hogwarts.edu 2006-11-05 23:35:27.560 bl***@hogwarts.edu 0
38 po****@hogwarts.edu 2006-11-05 23:36:12.983 bl***@hogwarts.edu 0
39 ma****@heaven.net 2006-11-06 14:04:19.983 du********@hogwarts.edu 0
40 ma****@heaven.net 2006-11-06 14:11:28.373 pr**@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 = 'ma****@heaven.net'
)

the above statement turns a row of "1"s to a row of "0"s . Same happens
with
the OR statement.

[blvandeb]

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 =
'[e-mail]' )

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 = 'm*****@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'
)

[pbd22]

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 'm*****@heaven.net' (add_email)

[pbd22]

thank you bl******@yahoo.com!

that was it. the recordID idea was spot on.

thanks a ton!

pbd22 wrote:

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 'm*****@heaven.net' (add_email)

[--CELKO--]

You have several design problems and I think that once you fix it,
changing profiles will be very easy.

You have no relational keys (IDENTITY is by definition never a key, so
you have destroyed data integrity) and more NULL-able columns than a
FORTUNE 100 payroll system. I also see that you have both
"add_email" (which is a procedure name in ISO conventions) and
"email_address" in the table; what does that mean? Why are they
logically different? If you have multiple email addresses in the data,
then use an address type to indicate this.

You have both "about_me" and "interest" which is very vague.
If they are different tell me in the data element name and add a
constraint to enforce it. What kind of interest or is interest a
percentage rate?

Why not put all the data into one table, with ISO-11179 data element
names and no redundant columns, and then create a VIEW for the profile
histories? Maybe like this:

CREATE TABLE UserProfileHistory
(email_addr VARCHAR(50) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME,
addr_type INTEGER NOT NULL,
headline VARCHAR(50) NOT NULL,
public_name VARCHAR(50) NOT NULL,
public_interests TEXT NOT NULL,
PRIMARY KEY (addr_email, start_date));

CREATE VIEW AddressList (email_addr, ..) with the last edited profile
for bulk mailings

You use a procedure to insert a new row whenever the profile is
changed. What you have done is mimic a magnetic tape file system; the
updates are on one tape whcih gets merged into the master tape. The
rows have no proper keys but do have an exposed physical locator -- a
fake record number! You used the proprietary BIT data type to mimic a
punch card or assembly language programming convention.

[pbd22]

--CELKO-- wrote:

You have several design problems and I think that once you fix it,
changing profiles will be very easy.

You have no relational keys (IDENTITY is by definition never a key, so
you have destroyed data integrity)

i am using email addresses as relational keys. each and every table has
an email
associated with each row. data is accessible in this way.

>and more NULL-able columns than a
FORTUNE 100 payroll system. I also see that you have both
"add_email" (which is a procedure name in ISO conventions) and
"email_address" in the table; what does that mean? Why are they
logically different? If you have multiple email addresses in the data,
then use an address type to indicate this.

add_email describes a profile that user "email_address" has added to
his AddList.
i am not aware of address types. i have been entering email addresses
as VARCHARS
which has not caused me any problems so far. is there a reason why i
should be
concerned about this?

You have both "about_me" and "interest" which is very vague.
If they are different tell me in the data element name and add a
constraint to enforce it. What kind of interest or is interest a
percentage rate?

this is part of the profile owner's description. about_me is TEXT and
describes
the users background. interests, again TEXT, is more specific. How
would
a constraint matter?

Why not put all the data into one table, with ISO-11179 data element
names and no redundant columns, and then create a VIEW for the profile
histories? Maybe like this:

CREATE TABLE UserProfileHistory
(email_addr VARCHAR(50) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME,
addr_type INTEGER NOT NULL,
headline VARCHAR(50) NOT NULL,
public_name VARCHAR(50) NOT NULL,
public_interests TEXT NOT NULL,
PRIMARY KEY (addr_email, start_date));

CREATE VIEW AddressList (email_addr, ..) with the last edited profile
for bulk mailings

You use a procedure to insert a new row whenever the profile is
changed. What you have done is mimic a magnetic tape file system; the
updates are on one tape whcih gets merged into the master tape. The
rows have no proper keys but do have an exposed physical locator -- a
fake record number! You used the proprietary BIT data type to mimic a
punch card or assembly language programming convention.

I am not sure if that is correct about the BIT values. The value
indicates an action
taken, such as a rejected or accepted request, and I dont see how else
to do this.
or, i dont see the point to improving on this method. what is being
harmed by this?

I like the idea of having an overall table for my profiles, as there
are about 30
individual tables at this point that describe different features of the
profile (EditProfile,
AddList, PersonalPhotos, etc). I am not quite sure how to procede doing
this. I'll
do some reading on this, in the mean time; am i doing any harm by
continuing to
build my system the way i am? can the aggregate table be created at a
later date
without much reverse engineering?

i thank you for your constructive feedback.

regards.

[Erland Sommarskog]

pbd22 (du*****@gmail.com) writes:

I am not sure if that is correct about the BIT values. The value
indicates an action taken, such as a rejected or accepted request, and I
dont see how else to do this. or, i dont see the point to improving on
this method. what is being harmed by this?

Absolutely nothing at all. Some people prefer to have a char(1) column
that permits two values, but there are different suggests of which values,
so a bit with 0/1 is as clearcut as it could be.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[othellomy]

I was wondering if multiple updates are happening that is overwriting
previous updates. The table in the where clause has '...in (some
values)...' If '...in (some values)...' are hit twice then the latest
value will overwrite other updates. I hope this makes sense.

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 = 'm*****@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'
)

[pbd22]

well, i'll keep an eye open for future problems, but right now the
statement is working fine.
taken from the server command statement, the final result is as follows
and seems to work:

update addlist set add_status = 1 where add_id in (select add_id from
addlist a, editprofile e where a.email_address = e.email_address and
e.public_name = '" + Request.Params.Get("pubname") + "' and a.add_email
= '" + Context.User.Identity.Name + "')

Erland:

thanks. that is what i thought. when any action taken on the client is
likely to produce a boolean result, storing the logic as a bit value
makes perfect sense to me. i am not sure
why the other fellow was so against it.

ot*******@yahoo.com wrote:

I was wondering if multiple updates are happening that is overwriting
previous updates. The table in the where clause has '...in (some
values)...' If '...in (some values)...' are hit twice then the latest
value will overwrite other updates. I hope this makes sense.

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 = 'm*****@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'
)

[--CELKO--]

>I am using email addresses as relational keys. each and every table has an email associated with each row. <<

Good; make sure that you have ON DELETE/UPDATE CASCADE clauses on the
referencing tables. m

>this is part of the profile owner's description. about_me is TEXT and describes the users background. interests, again TEXT, is more specific. <<

Have you ever filled out a survey card that came with a consumer
product? They often have a list of interests (travel, boating, movies,
etc.) -- that is how you encode specific data like interest and not in
free-form text.

>I am not sure if that is correct about the BIT values. The value indicates an action taken, such as a rejected or accepted request, and I dont see how else to do this. <<

After 20+ years with SQL and RDBMS, I am sure. You are using
low-level, highly proprietary data types. Give me the action and the
date that the action was taken, not an assembly language style flag.

>or, i dont see the point to improving on this method. what is being harmed by this? <<

Growth of the code, validation against the rest of the history and data
loss.

>I like the idea of having an overall table for my profiles, as there are about 30 individual tables at this point that describe different features of the profile (EditProfile, AddList, PersonalPhotos, etc). I am not quite sure how to procede doing this. I'll do some reading on this, in the mean time; am i doing any harm by continuing to build my system the way i am? can the aggregate table be created at a later date without much reverse engineering? <<

I cannot say; I have no specs. But 30 tables seems like a lot for a
personal profile and perhaps a bit low for a full medical history.
Instead of pushing ahead with a weak design and then trying to fix it,
I would stop and spend the effort on getting it right at the start.

There is the "cowboy coder" impluse to code something in two minutes
with proprietary tricks and a short-term view of efficient architecture
rather than take two days (or even two months!) to research standards
and come up with a consistent, portable, verifiable design.

[Tony Rogerson]

You don't mention CHECK constraints on the email address - we are still
waiting for you to show us how you do that in a CHECK CONSTRAINT.

You seem to think you can make reference to other tables and call a stored
procedure from within a CHECK CONSTRAINT.

Are you going to finally admit you are wrong and that the only reasonable
way to validate an email address in a CHECK CONSTRAINT is CLR?

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.netwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

>>I am using email addresses as relational keys. each and every table has
an email associated with each row. <<

Good; make sure that you have ON DELETE/UPDATE CASCADE clauses on the
referencing tables. m

>>this is part of the profile owner's description. about_me is TEXT and
describes the users background. interests, again TEXT, is more specific.
<<

Have you ever filled out a survey card that came with a consumer
product? They often have a list of interests (travel, boating, movies,
etc.) -- that is how you encode specific data like interest and not in
free-form text.

>>I am not sure if that is correct about the BIT values. The value
indicates an action taken, such as a rejected or accepted request, and I
dont see how else to do this. <<

After 20+ years with SQL and RDBMS, I am sure. You are using
low-level, highly proprietary data types. Give me the action and the
date that the action was taken, not an assembly language style flag.

>>or, i dont see the point to improving on this method. what is being
harmed by this? <<

Growth of the code, validation against the rest of the history and data
loss.

>>I like the idea of having an overall table for my profiles, as there are
about 30 individual tables at this point that describe different
features of the profile (EditProfile, AddList, PersonalPhotos, etc). I
am not quite sure how to procede doing this. I'll do some reading on
this, in the mean time; am i doing any harm by continuing to build my
system the way i am? can the aggregate table be created at a later date
without much reverse engineering? <<

I cannot say; I have no specs. But 30 tables seems like a lot for a
personal profile and perhaps a bit low for a full medical history.
Instead of pushing ahead with a weak design and then trying to fix it,
I would stop and spend the effort on getting it right at the start.

There is the "cowboy coder" impluse to code something in two minutes
with proprietary tricks and a short-term view of efficient architecture
rather than take two days (or even two months!) to research standards
and come up with a consistent, portable, verifiable design.

[--CELKO--]

>Are you going to finally admit you are wrong and that the only reasonable way to validate an email address in a CHECK CONSTRAINT is CLR? <<

The way you do it in a CHECK() is with a SIMILAR TO predicate, as per
ANSI/ISO Standard SQL and not by violating Codd's rules. (12. The
nonsubversion rule: If the system provides a low-level
(record-at-a-time) interface, then that interface cannot be used to
subvert the system (e.g.) bypassing a relational security or integrity
constraint.)

The way you do it in SQL Server (until they come up to Standard SQL) is
with a CREATE VIEW .. WITH CHECK OPTION;

If you do not know the trick, let me give a simple example. I have an
adjacency list model tree and I want to enforce the rule that (number
of nodes) = (number of edges) -1, which is a necessary but not
sufficient condition for a tree.

-- Standard SQL
CREATE TABLE Tree
(parent_node CHAR(1), -- null is root
child_node CHAR(1) NOT NULL,
UNIQUE (parent_node, child_node),
CONSTRAINT no_simple_loops
CHECK (parent_node <child_node), -- easy table level constaint
CONSTRAINT no_extra_edges
CHECK ((SELECT COUNT(*) FROM Tree) - 1
= (SELECT COUNT(*)
FROM ((SELECT parent_node FROM Tree)
UNION
(SELECT child_node FROM Tree)) AS X(node)
),
etc.);

--SQL Server workaround

CREATE VIEW MyTree (parent_node, child_node)
AS
SELECT T1.parent_node, T1.child_node
FROM Tree AS T1
WHERE NOT EXISTS
(((SELECT COUNT(*) FROM Tree) - 1
<((SELECT COUNT(*)
FROM ((SELECT parent_node FROM Tree)
UNION
(SELECT child_node FROM Tree)) AS X(node)
)
WITH CHECK OPTION;

I would put the nodes in a VIEW or a CTE since that could be useful in
other places. Then use DCL to keep users away from the basr table.
This converts a complex CHECK() with subqueries into a WHERE clause.
It is a standard SQL programming trick that goes back over a decade ago
and was popuilar with DB2 programmers.

[Tony Rogerson]

Oh - my - god; and how many levels does "etc..." cover in your example?

Also, it still doesn't properly validate for an email address - post some
workable, essentially you can't, its not practical nor possible in Standard
SQL as implemented by Microsoft SQL Server to date.

The fact that you would really sacrifice performance, scalability and
concurrency just to be so anal in not using tried, tested and recommend
approaches for complex data validation in SQL Server 2005 (CLR) speaks
volumes; I sure hope you don't recommend these types of solutions to "your
clients", if so - let me have a list of those on SQL Server so I can go and
fix their performance and concurrency problems, the saving in hardware and
application rewrite costs alone; my fee is a fraction to what they will save
and I'm not cheap - well, I charge tripple what you do if that's any
indicator.

Like I say, get out of your class room, get some real industrial experience
and do us all a favour.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.netwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

>>Are you going to finally admit you are wrong and that the only
reasonable way to validate an email address in a CHECK CONSTRAINT is
CLR? <<

The way you do it in a CHECK() is with a SIMILAR TO predicate, as per
ANSI/ISO Standard SQL and not by violating Codd's rules. (12. The
nonsubversion rule: If the system provides a low-level
(record-at-a-time) interface, then that interface cannot be used to
subvert the system (e.g.) bypassing a relational security or integrity
constraint.)

The way you do it in SQL Server (until they come up to Standard SQL) is
with a CREATE VIEW .. WITH CHECK OPTION;

If you do not know the trick, let me give a simple example. I have an
adjacency list model tree and I want to enforce the rule that (number
of nodes) = (number of edges) -1, which is a necessary but not
sufficient condition for a tree.

-- Standard SQL
CREATE TABLE Tree
(parent_node CHAR(1), -- null is root
child_node CHAR(1) NOT NULL,
UNIQUE (parent_node, child_node),
CONSTRAINT no_simple_loops
CHECK (parent_node <child_node), -- easy table level constaint
CONSTRAINT no_extra_edges
CHECK ((SELECT COUNT(*) FROM Tree) - 1
= (SELECT COUNT(*)
FROM ((SELECT parent_node FROM Tree)
UNION
(SELECT child_node FROM Tree)) AS X(node)
),
etc.);

--SQL Server workaround

CREATE VIEW MyTree (parent_node, child_node)
AS
SELECT T1.parent_node, T1.child_node
FROM Tree AS T1
WHERE NOT EXISTS
(((SELECT COUNT(*) FROM Tree) - 1
<((SELECT COUNT(*)
FROM ((SELECT parent_node FROM Tree)
UNION
(SELECT child_node FROM Tree)) AS X(node)
)
WITH CHECK OPTION;

I would put the nodes in a VIEW or a CTE since that could be useful in
other places. Then use DCL to keep users away from the basr table.
This converts a complex CHECK() with subqueries into a WHERE clause.
It is a standard SQL programming trick that goes back over a decade ago
and was popuilar with DB2 programmers.

[onedaywhen]

On Nov 12, 8:48 am, "Tony Rogerson" <[e-mail]>
wrote:

Are you going to finally admit you are wrong and that the only reasonable
way to validate an email address in a CHECK CONSTRAINT is CLR?

Tony,
Please clarify which aspects of an email address you consider cannot be
validated via TSQL.

I know the things that can be validated using basic pattern matching
(number of characters, exactly one 'commercial at', at least one period
following the 'commercial app', etc) so what do you have in mind that
can only be done via CLR? I assume we can agree that verification (e.g.
can I send mail to this email address) is out of scope.

TIA,
Jamie.

--

[--CELKO--]

>I know the things that can be validated using basic pattern matching .. what do you have in mind that can only be done via CLR? <<

Google for a previous posting in which I outlined a truly brutal query
technique that simulates a full regular expression parser in pure SQL
(+ and * were done with look-up tables to build the limited patterns
available in T-SQL).

I got an email from a friend who has written grep() from scratch in his
youth that he thinks he can do it with a recursive CTE. He is a DB2
user (they are up to Standards), so he does not need it, but it is a
good exercise. I will see if he comes up with anything when I get back
from PASS.

I keep saying this, but SQL Server needs to come up to Standard and
have a SIMILAR TO predicate like other products. Regular Expressions
are just too handy for validation.

>I assume we can agree that verification (e.g. can I send mail to this email address) is out of scope. <<

In practice, that is the most important point; verification is stronger
than validation. People actually make few errors in the syntax of an
email address, but do make typos in the text of the email address.
Thus a simple pattern match for at-sign, proper suffixes and common
email providers validates better than 99.9% of the actual cases.

The other statistic I want to find is how long people keep the same
email address. I have heard 5+ years. The reason is that people tend
to keep an account with a provider and not move around. Yyou have your
monthly payment set up as an automatic withdrawal, everyone knows this
email, etc. -- same thing happened with land line phones

This is unlike cell phones where people shop for a good deal at the end
of each contract.

[Tony Rogerson]

Hi Jamie,

Its all about complexity, its doable in T-SQL but its very long winded and
hard to understand/maintain - compared to the regurlar expressions
available.

You need to consider a) valid characters, b) all the dots are in place, c)
there is an @ in the right place, thats the easy stuff, but then there are
domains/sub-domains to consider which means you need to start thinking
recursively....

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"onedaywhen" <ja**********@xsmail.comwrote in message
news:11*********************@b28g2000cwb.googlegro ups.com...

>
On Nov 12, 8:48 am, "Tony Rogerson" <[e-mail]>
wrote:

>Are you going to finally admit you are wrong and that the only reasonable
way to validate an email address in a CHECK CONSTRAINT is CLR?

Tony,
Please clarify which aspects of an email address you consider cannot be
validated via TSQL.

I know the things that can be validated using basic pattern matching
(number of characters, exactly one 'commercial at', at least one period
following the 'commercial app', etc) so what do you have in mind that
can only be done via CLR? I assume we can agree that verification (e.g.
can I send mail to this email address) is out of scope.

TIA,
Jamie.

--