Having problems with update statement

Can someone help me with this update, I have exhausted
all my effort into resolving this -
update pfile set facility = (select max(a.facility_num)
from efile a
where p.Order_num = a.Order_num and
pfile.facility <> a.facility_num and
a.facility_num IS NOT NULL and
pfile.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num)
I checked the corresponding select statement works
select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
b.facility
from efile a, pfile b
where a.Order_num = b.Order_num and a.facility_num <> b.facility
and b.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num
This works with group by clause too.
When I run update statement, I get
"Cannot insert null values into column facility "
However, I checked there is no null value in the select
statement or existing data. Then I changed the column
to allow nulls, it put null value for all records.
I also tried the statement without max(facility_num)
as there isn't more than one record as of now.
Still I get "Cannot insert null value " error, any ideas
how to resolve this?
I am stuck, I have to meeet deadline.
Thanks for your help!
-MYou need to keep your aliasing straight. You were mixing a "p" alias and the
base table name without ever defining it. Also, when a correlated sub-query
does not return any results, its value is NULL. Your updatable column does
not allow NULLs; so, you have to code for that condition either by excluding
those updates--which I coded--or using something like a CASE statement or the
ISNULL function to provide a different value when NULL appears.
Here is my alternative:
UPDATE p
SET facility = (SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
FROM pfile AS p
WHERE p.facility LIKE '9999%'
AND EXISTS(
SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
Now, if you give it some thought, you should be able to upgrade this from a
correlated sub-query expression to a direct update using multiple table
joins. This would be preferrable because the statement above will be
sloooooooow.
Good luck.
Sincerely,
Anthony Thomas
"Me" wrote:
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>|||Anthony,
Thanks for the reply!
Still it didn't work, but I found a work around.
Appreciate your help!
-M
"AnthonyThomas" wrote:
> You need to keep your aliasing straight. You were mixing a "p" alias and the
> base table name without ever defining it. Also, when a correlated sub-query
> does not return any results, its value is NULL. Your updatable column does
> not allow NULLs; so, you have to code for that condition either by excluding
> those updates--which I coded--or using something like a CASE statement or the
> ISNULL function to provide a different value when NULL appears.
> Here is my alternative:
> UPDATE p
> SET facility => (SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> FROM pfile AS p
> WHERE p.facility LIKE '9999%'
> AND EXISTS(
> SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> Now, if you give it some thought, you should be able to upgrade this from a
> correlated sub-query expression to a direct update using multiple table
> joins. This would be preferrable because the statement above will be
> sloooooooow.
> Good luck.
> Sincerely,
>
> Anthony Thomas
>
> "Me" wrote:
> > Can someone help me with this update, I have exhausted
> > all my effort into resolving this -
> >
> > update pfile set facility = (select max(a.facility_num)
> > from efile a
> > where p.Order_num = a.Order_num and
> > pfile.facility <> a.facility_num and
> > a.facility_num IS NOT NULL and
> > pfile.facility like '9999%'
> > and a.facility_num not like 'N/A%' group by a.Order_num)
> >
> >
> > I checked the corresponding select statement works
> >
> > select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> > b.facility
> > from efile a, pfile b
> > where a.Order_num = b.Order_num and a.facility_num <> b.facility
> > and b.facility like '9999%'
> > and a.facility_num not like 'N/A%' group by a.Order_num
> >
> > This works with group by clause too.
> >
> > When I run update statement, I get
> > "Cannot insert null values into column facility "
> >
> > However, I checked there is no null value in the select
> > statement or existing data. Then I changed the column
> > to allow nulls, it put null value for all records.
> >
> > I also tried the statement without max(facility_num)
> > as there isn't more than one record as of now.
> > Still I get "Cannot insert null value " error, any ideas
> > how to resolve this?
> >
> > I am stuck, I have to meeet deadline.
> >
> > Thanks for your help!
> > -M
> >
> >
> >
> >|||Um, you forgot your WHERE clause on your update statement. As written, it
would try to update every row in pfile.
And you don't have a table corresponding to your p alias.
Jeff
"Me" <Me@.discussions.microsoft.com> wrote in message
news:143D3A21-3743-4F66-8B2E-91764A750E62@.microsoft.com...
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>sql

Having problems with update statement

Can someone help me with this update, I have exhausted
all my effort into resolving this -
update pfile set facility = (select max(a.facility_num)
from efile a
where p.Order_num = a.Order_num and
pfile.facility <> a.facility_num and
a.facility_num IS NOT NULL and
pfile.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num)
I checked the corresponding select statement works
select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
b.facility
from efile a, pfile b
where a.Order_num = b.Order_num and a.facility_num <> b.facility
and b.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num
This works with group by clause too.
When I run update statement, I get
"Cannot insert null values into column facility "
However, I checked there is no null value in the select
statement or existing data. Then I changed the column
to allow nulls, it put null value for all records.
I also tried the statement without max(facility_num)
as there isn't more than one record as of now.
Still I get "Cannot insert null value " error, any ideas
how to resolve this?
I am stuck, I have to meeet deadline.
Thanks for your help!
-M
You need to keep your aliasing straight. You were mixing a "p" alias and the
base table name without ever defining it. Also, when a correlated sub-query
does not return any results, its value is NULL. Your updatable column does
not allow NULLs; so, you have to code for that condition either by excluding
those updates--which I coded--or using something like a CASE statement or the
ISNULL function to provide a different value when NULL appears.
Here is my alternative:
UPDATE p
SET facility =
(SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
FROM pfile AS p
WHERE p.facility LIKE '9999%'
AND EXISTS(
SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
Now, if you give it some thought, you should be able to upgrade this from a
correlated sub-query expression to a direct update using multiple table
joins. This would be preferrable because the statement above will be
sloooooooow.
Good luck.
Sincerely,
Anthony Thomas
"Me" wrote:

> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>
|||Anthony,
Thanks for the reply!
Still it didn't work, but I found a work around.
Appreciate your help!
-M
"AnthonyThomas" wrote:
[vbcol=seagreen]
> You need to keep your aliasing straight. You were mixing a "p" alias and the
> base table name without ever defining it. Also, when a correlated sub-query
> does not return any results, its value is NULL. Your updatable column does
> not allow NULLs; so, you have to code for that condition either by excluding
> those updates--which I coded--or using something like a CASE statement or the
> ISNULL function to provide a different value when NULL appears.
> Here is my alternative:
> UPDATE p
> SET facility =
> (SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> FROM pfile AS p
> WHERE p.facility LIKE '9999%'
> AND EXISTS(
> SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> Now, if you give it some thought, you should be able to upgrade this from a
> correlated sub-query expression to a direct update using multiple table
> joins. This would be preferrable because the statement above will be
> sloooooooow.
> Good luck.
> Sincerely,
>
> Anthony Thomas
>
> "Me" wrote:
|||Um, you forgot your WHERE clause on your update statement. As written, it
would try to update every row in pfile.
And you don't have a table corresponding to your p alias.
Jeff
"Me" <Me@.discussions.microsoft.com> wrote in message
news:143D3A21-3743-4F66-8B2E-91764A750E62@.microsoft.com...
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>

Having problems with update statement

Can someone help me with this update, I have exhausted
all my effort into resolving this -
update pfile set facility = (select max(a.facility_num)
from efile a
where p.Order_num = a.Order_num and
pfile.facility <> a.facility_num and
a.facility_num IS NOT NULL and
pfile.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num)
I checked the corresponding select statement works
select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
b.facility
from efile a, pfile b
where a.Order_num = b.Order_num and a.facility_num <> b.facility
and b.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num
This works with group by clause too.
When I run update statement, I get
"Cannot insert null values into column facility "
However, I checked there is no null value in the select
statement or existing data. Then I changed the column
to allow nulls, it put null value for all records.
I also tried the statement without max(facility_num)
as there isn't more than one record as of now.
Still I get "Cannot insert null value " error, any ideas
how to resolve this?
I am stuck, I have to meeet deadline.
Thanks for your help!
-MYou need to keep your aliasing straight. You were mixing a "p" alias and th
e
base table name without ever defining it. Also, when a correlated sub-query
does not return any results, its value is NULL. Your updatable column does
not allow NULLs; so, you have to code for that condition either by excluding
those updates--which I coded--or using something like a CASE statement or th
e
ISNULL function to provide a different value when NULL appears.
Here is my alternative:
UPDATE p
SET facility =
(SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
FROM pfile AS p
WHERE p.facility LIKE '9999%'
AND EXISTS(
SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
Now, if you give it some thought, you should be able to upgrade this from a
correlated sub-query expression to a direct update using multiple table
joins. This would be preferrable because the statement above will be
sloooooooow.
Good luck.
Sincerely,
Anthony Thomas
"Me" wrote:

> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>|||Anthony,
Thanks for the reply!
Still it didn't work, but I found a work around.
Appreciate your help!
-M
"AnthonyThomas" wrote:
[vbcol=seagreen]
> You need to keep your aliasing straight. You were mixing a "p" alias and
the
> base table name without ever defining it. Also, when a correlated sub-que
ry
> does not return any results, its value is NULL. Your updatable column doe
s
> not allow NULLs; so, you have to code for that condition either by excludi
ng
> those updates--which I coded--or using something like a CASE statement or
the
> ISNULL function to provide a different value when NULL appears.
> Here is my alternative:
> UPDATE p
> SET facility =
> (SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> FROM pfile AS p
> WHERE p.facility LIKE '9999%'
> AND EXISTS(
> SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> Now, if you give it some thought, you should be able to upgrade this from
a
> correlated sub-query expression to a direct update using multiple table
> joins. This would be preferrable because the statement above will be
> sloooooooow.
> Good luck.
> Sincerely,
>
> Anthony Thomas
>
> "Me" wrote:
>|||Um, you forgot your WHERE clause on your update statement. As written, it
would try to update every row in pfile.
And you don't have a table corresponding to your p alias.
Jeff
"Me" <Me@.discussions.microsoft.com> wrote in message
news:143D3A21-3743-4F66-8B2E-91764A750E62@.microsoft.com...
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>

HAVING (COUNT(category) > 1) , not only 1 row

i am using this code :

SELECT MAX(user) AS lastuser, category
FROM journal
GROUP BY category
HAVING (COUNT(category) > 1)

it works but returns 1 line by category >1

i need all the user (all the rows) HAVING (COUNT(category) > 1) , not only 1

if 1 category has only 1 user i must not keep it

i am not shure to be clear :-)

thank you for helpingIf you need all the users fulfilling the requirements, why are you using the "max" argument. By its nature, the max (maximum) will return the largest value. Get rid of max, add user to your group by and try again.|||you mean

SELECT user, category
FROM journal
GROUP BY user,category
HAVING (COUNT(category) > 1)

i dont get it in that way

I dont need the lines : A|B where count(B) = 1
i need only the lines : A|B. C|B, D|B count(B) > 1

with my first code I get D|B (the last one)|||I don't think that your query will work it would return
where a user had the same category more than once
not differeent categories for the same user.

SELECT user
FROM journal
GROUP BY user
HAVING (COUNT(category) > 1)|||i dont get it in that way
i need for exemple 3 lines

john tennis
pierre tennis
paul tennis

3 lines if count(category) > 1|||SSELECT user_name,
category
FROM journal
WHERE category IN (SELECT category
FROM journal
GROUP BY category
HAVING COUNT(*) > 1)|||i'll try

thank you

Handling UNC path names

What is the best handling of a UNC Pathname?

What is the max length of a path name?
Which is the best type of field in SQL Server to use for a path name?

Thanks

I would store this in a VARCHAR field with the appropiate value.

HTH, Jens Suessmeyer,

|||

What is the max length of the path & filename? It used to be 255 chars but I believe it has grown since then.

|||

Maximum name of a UNC path is 260 unicode characters (MAX_PATH defined in windows.h I believe). Search google for MAX_PATH to see lots of people talking about this issue.

So a NVARCHAR(260) should be sufficient unless you allow the \\?\ prefix that bypasses normal MAX_PATH length. If you only have one locale you use for paths, then you can potentially use VARCHAR(260).