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