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 problems creating an SQL statement

I am having trouble getting the SQL statement to return stats from a survey the way I want them. The table is set up as:

ID Q1 Q2 Q3 Q4

Responses for each question (Columns Q1 – Q4) will be a numerical value between 1-5. I want to count how many 1s, 2s, 3s, etc. I have tried different joins, self joins, unions and sub selections but cannot get the correct output.

I would like to get the output for each question as a single record, and if possible have a final column with an average for the question. But I can do that in the data binding if needed.

Qs Ones Twos Threes Fours Fives

Q1 #of 1s #of 2s #of 3s #of 4s #of 5s

Q2 #of 1s #of 2s #of 3s #of 4s #of 5s

Q3 #of 1s #of 2s #of 3s #of 4s #of 5s

Any tips or SQL sample statements would be greatly appreciated.

It looks like 2 pivots will be needed to to transpose the data from your table layout to your desired output layout. If you're using Sql 2005, there is a Pivot feature but here i'll show you how you could do this using syntax that will work for Sql2000 or Sql2005.

I'll break each step down into it's own View. Each view will build off of the previous view(s).

The 1st View is named: VIEW_SurveyRotation1
In this View, we turn all the column headings (Q1, Q2 etc..) into data values and move all the answers into a single Answer column. This is actually the opposite of what we normally consider a pivot, but i still tend to think of it as a [reverse] pivot.

SELECT'Q1'AS Qs, Q1AS answerFROM dbo.SurveyUNIONALLSELECT'Q2'AS Qs, Q2AS answerFROM dbo.SurveyUNIONALLSELECT'Q3'AS Qs, Q3AS answerFROM dbo.SurveyUNIONALLSELECT'Q4'AS Qs, Q4AS answerFROM dbo.Survey

The 2nd View is named: VIEW_SurveyRotation2
In this View, we pivot the answer values back into column headings by querying against the results of our first View. As mentioned, there's more than one way to create a Pivot in Sql.

SELECT Qs,CASEWHEN answer = 1THEN 1ELSE 0END AS One,CASEWHEN answer = 2THEN 1ELSE 0END AS Two,CASEWHEN answer = 3THEN 1ELSE 0END AS Three,CASEWHEN answer = 4THEN 1ELSE 0END AS Four,CASEWHEN answer = 5THEN 1ELSE 0END AS FiveFROM dbo.VIEW_SurveyRotation1

The 3rd View is named: VIEW_SurveyAverages
Here we can create a simple set of Averages by querying against our first View

SELECT Qs,AVG(CAST(answerAS decimal))AS [Avg]FROM dbo.VIEW_SurveyRotation1GROUP BY Qs

The 4th and final View is named: VIEW_SurveyResult
In this View, we summarize the answer counts for each column and also join in the Averages

SELECT dbo.VIEW_SurveyRotation2.Qs, dbo.VIEW_SurveyAverages.[Avg],SUM(dbo.VIEW_SurveyRotation2.One)AS Ones,SUM(dbo.VIEW_SurveyRotation2.Two)AS Twos,SUM(dbo.VIEW_SurveyRotation2.Three)AS Threes,SUM(dbo.VIEW_SurveyRotation2.Four)AS FoursFROM dbo.VIEW_SurveyRotation2INNERJOIN dbo.VIEW_SurveyAveragesON dbo.VIEW_SurveyRotation2.Qs = dbo.VIEW_SurveyAverages.QsGROUP BY dbo.VIEW_SurveyRotation2.Qs, dbo.VIEW_SurveyAverages.[Avg]

I tend to work with complex sql queries by breaking it down into steps like this. It helps me to achieve the desired result. Then, once you've got it working, you can review it and see if you can eliminate any of the steps by consolidating them into fewer queries.

|||

Here is the code sample for UNPIVOT and PIVOT solution with SQL Server 2005:

SELECT Questionas Qs, [1]as Ones, [2]as Twos, [3]as Threes, [4]as Fours, [5]as FivesFROM

(SELECT Question, [Value]FROM pivotQuestions

UNPIVOT([Value]FOR [Question]in([Q1], [Q2], [Q3], [Q4], [Q5], [Q6])

)as unpvt) t

PIVOT(COUNT([Value])FOR [Value]IN([1], [2], [3], [4], [5])

)as pvt

--Table and test data

CREATETABLE [dbo].[pivotQuestions](

[ID] [int]NotNULL,

[Q1] [int]NULL,

[Q2] [int]NULL,

[Q3] [int]NULL,

[Q4] [int]NULL,

[Q5] [int]NULL,

[Q6] [int]NULL

)

GO

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(1, 3, 4, 5, 5, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(2, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(3, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(4, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(5, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(6, 3, 4, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(7, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(8, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(9, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(10, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(11, 5, 1, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(12, 2, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(13, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(14, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(15, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(16, 5, 1, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(17, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(18, 5, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(19, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(20, 5, 5, 5, 5, 5, 5)

|||

Thanks for the tips, I will give them a try.

|||

Do not have SQL 2005 so I did what you suggested mbanavige. I was able to combine it all into a single query and not use Views, just couldnt add in the averages that. But that was easy enough to do on the databind. Working like a charm. thanks.

|||

limno:

Here is the code sample for UNPIVOT and PIVOT solution with SQL Server 2005:

SELECT Questionas Qs, [1]as Ones, [2]as Twos, [3]as Threes, [4]as Fours, [5]as FivesFROM

(SELECT Question, [Value]FROM pivotQuestions

UNPIVOT([Value]FOR [Question]in([Q1], [Q2], [Q3], [Q4], [Q5], [Q6])

)as unpvt) t

PIVOT(COUNT([Value])FOR [Value]IN([1], [2], [3], [4], [5])

)as pvt

Hi,

I'm having a really similar problem, also with surveys.

The only 2 differences are that (a) I don't want to summarise my results at all (b) I have multiple surveys in the same table so need to use an extra clause to pick out info for the survey I am interested in.

So far I have come up with...

TABLE

=====

SurveyID RespondantID QuestionID Answer

PIVOT QUERY

===========

SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]
As Q10 FROM (SELECT RespondantlD, QuestionlD, Answer FROM "3_Temp" WHERE SurveylD=3) AS preData PIVOT (
COUNT(Answer) FOR QuestionlD IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) ) AS data ORDER BV RespondantlD

But it doesn't work and I can't figure out why.

What am I doing wrong?

HAVING clause?

Hi all,

How would I add:

WHERE Year(tblDetails.DateAdded)=#2003#

... to the SQL statement below?

"SELECT tblDetails.ProductID, tblProducts.ShortDesc, Count(tblDetails.ProductID) AS ProductCount FROM (tblDetails INNER JOIN tblProducts ON tblDetails.ProductID = tblProducts.ProductID) GROUP BY tblDetails.ProductID, tblProducts.ShortDesc ORDER BY Count(tblDetails.ProductID) DESC"

Cheers,
Davidi think the order is

select...
from...
where...
group by ...
having...
order by...

sql

HAVING clause is a case statement??

i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table:

(simple ex)

Select order_no
from table1
inner join table2
on table1.order_no = table2.order_no
group by order_no
having table1.Qty<> table2.Qty

BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes:

CASE WHEN table1.Qty<> table3.Qty
THEN table3.Qty<> table2.Qty
ELSE table1.Qty<> table2.Qty END

but how do i actually write this?perhaps if you would care to explain what you're doing?

are you comparing individual Qty values, or the SUMs?

because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions|||perhaps if you would care to explain what you're doing?

are you comparing individual Qty values, or the SUMs?

because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions

Sorry I am trying to compare Sum(qty) for each product in an order (product maybe in the order more than 1ce) I am trying to retrieve any product lines where Sum qties in table1 and table2 are not the same.

However, if stock was not found, then an allocated qty is recorded in table 3...so in this case I want to compare qtyies in table3 and table2
?|||select t1.order_no
, t1.sumqty
, t2.order_no
, t2.sumqty
from (
select order_no
, sum(qty) as sumqty
from table1
group
by order_no
) as t1
full outer
join (
select order_no
, sum(qty) as sumqty
from table2
group
by order_no
) as t2
on t2.order_no = t1.order_no
and t2.sumqty <> t1.sumqtythat's the general strategy -- do your sums in derived tables

for table 3, you're on your own :)

having a select statement with no output

i have the following select query "select myCol from myTbl"

i cant modify the query but i can add to it;
what i want is to cancel the output of this query so it has no output( just like an update or insert query). a way for doing this is using 'select into' a #temp table but i cant do this becoz adding the 'into' at the end wont work after the 'from'.

i can use 'union all' to add another select statement but i cant use 'into' in the second statement becoz it has to be in the first one.

adding 'where 1=2' is not what i want becoz it still gives an empty output

any way to do this ?

thxCan you only add to the end of it? If you just add "--" to the front of it it won't execute at all, and you won't get any output.

This is certainly one of the strange requests I've seen on this forum. Why do you wan't a select statements with no output? Are you doing debugging or some sort of iterative process automation?

blindman|||thx for ur reply

i cant add to the front becoz it's a written query that i cant modify but can only add a string to the end before it is executed.

my prob is solved now after i found my source files and now i can modify the query..

thx|||I STILL have no idea...

WOAC radio

Have Insert statement, need equivalent Update.

Using ms sql 2000
I have 2 tables.
I have a table which has information regarding a computer scan. Each
record in this table has a column called MAC which is the unique ID for
each Scan. The table in question holds the various scan results of
every scan from different computers. I have an insert statement that
works however I am having troulbe getting and update statement out of
it, not sure if I'm using the correct method to insert and thats why or
if I'm just missing something. Anyway the scan results is stored as an
XML document(@.iTree) so I have a temp table that holds the relevent
info from that. Here is my Insert statement for the temporary table.
INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)
Now here is the insert statement for the table I am having trouble
with.
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
#temp.scanattribute, DateCreated = getdate(), LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp ON tblScanAttribute.Name =
#temp.Name
If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.Because your procedure don't use sp_executeSql you can use a table variable,
not a temp table.
Declare @.Tab table
(
Field1 nvarchar(10),
Field2 int,
...(exactly the fields in the xml file)
)
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, @.Tab.ID, @.TabParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
@.Tab.scanattribute, getdate(), getdate()
FROM tblScan
INNER JOIN tblScanAttribute
JOIN@.Tab ON tblScanAttribute.Name =
@.Tab.Name
Of course fields must match...
Hope it helps
Benga.
"rhaazy" <rhaazy@.gmail.com> wrote in message
news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...
> Using ms sql 2000
> I have 2 tables.
> I have a table which has information regarding a computer scan. Each
> record in this table has a column called MAC which is the unique ID for
> each Scan. The table in question holds the various scan results of
> every scan from different computers. I have an insert statement that
> works however I am having troulbe getting and update statement out of
> it, not sure if I'm using the correct method to insert and thats why or
> if I'm just missing something. Anyway the scan results is stored as an
> XML document(@.iTree) so I have a temp table that holds the relevent
> info from that. Here is my Insert statement for the temporary table.
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
> 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
>
> Now here is the insert statement for the table I am having trouble
> with.
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, #temp.ID, #temp.ParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> #temp.scanattribute, DateCreated = getdate(), LastModified =
> getdate()
> FROM tblScan, tblScanAttribute JOIN #temp ON tblScanAttribute.Name =
> #temp.Name
> If there is a way to do this without the temporary table that would be
> great, but I haven't figured a way around it yet, if anyone has any
> ideas that would be great, thanks.
>|||While this is good to know my real problem is that I need the statement
that will do what my insert does accept I need it to be an update
statement. I need the update because an insert is only going to happen
once for each client.
Benga wrote:
> Because your procedure don't use sp_executeSql you can use a table variabl
e,
> not a temp table.
> Declare @.Tab table
> (
> Field1 nvarchar(10),
> Field2 int,
> ...(exactly the fields in the xml file)
> )
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, @.Tab.ID, @.TabParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> @.Tab.scanattribute, getdate(), getdate()
> FROM tblScan
> INNER JOIN tblScanAttribute
> JOIN@.Tab ON tblScanAttribute.Name =
> @.Tab.Name
> Of course fields must match...
> Hope it helps
> Benga.
> "rhaazy" <rhaazy@.gmail.com> wrote in message
> news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...|||Fixed it, no problems.
rhaazy wrote:
> While this is good to know my real problem is that I need the statement
> that will do what my insert does accept I need it to be an update
> statement. I need the update because an insert is only going to happen
> once for each client.
> Benga wrote:

Have Insert statement, need equivalent Update.

Using ms sql 2000
I have 2 tables.
I have a table which has information regarding a computer scan. Each
record in this table has a column called MAC which is the unique ID for

each Scan. The table in question holds the various scan results of
every scan from different computers. I have an insert statement that
works however I am having troulbe getting and update statement out of
it, not sure if I'm using the correct method to insert and thats why or

if I'm just missing something. Anyway the scan results is stored as an

XML document(@.iTree) so I have a temp table that holds the relevent
info from that. Here is my Insert statement for the temporary table.

INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)

Now here is the insert statement for the table I am having trouble
with.

INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
#temp.scanattribute, DateCreated = getdate(),
LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp ON
tblScanAttribute.Name =
#temp.Name

If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.rhaazy (rhaazy@.gmail.com) writes:
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
> 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
> Now here is the insert statement for the table I am having trouble
> with.
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, #temp.ID, #temp.ParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> #temp.scanattribute, DateCreated = getdate(),
> LastModified =
> getdate()
> FROM tblScan, tblScanAttribute JOIN #temp ON
> tblScanAttribute.Name =
> #temp.Name
> If there is a way to do this without the temporary table that would be
> great, but I haven't figured a way around it yet, if anyone has any
> ideas that would be great, thanks.

I have some difficulties to understand what your problem is. If all
you want to do is to insert from the XML document, then you don't
need the temp table, but you could use OPENXML directly in the
query.

But then you talk about an UPDATE as well, and if your aim is to insert
new rows, and update existing, it's probably better to use a temp
table (or a table variable), so that you don't have to run OPENXML twice.
Some DB engines support a MERGE command which performs the task of
UPDATE and INSERT in one statement, but this is not available in
SQL Server, not even in SQL 2005.

If this did not answer your question, could you please clarify?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||My app runs on all my companies PCs every month a scan is performed and
the resulst are stored in a database. So the first time a scan is
performed for any PC it will be an insert, but after that it will
always be an update. I tried using openxml in my insert statement but
kept getting an error stating my sub query is returning more than one
result... So since I couldn't do it that way I'm trying this method.
All the relevent openxml is there I just couldn't figure out how to
insert each column using it. If you have any suggestions I'm open to
give it a try.

Erland Sommarskog wrote:
> rhaazy (rhaazy@.gmail.com) writes:
> > INSERT INTO #temp
> > SELECT * FROM openxml(@.iTree,
> > 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> > WITH(
> > ID nvarchar(50) './@.ID',
> > ParentID nvarchar(50) './@.ParentID',
> > Name nvarchar(50) './@.Name',
> > scanattribute nvarchar(50) '.'
> > )
> > Now here is the insert statement for the table I am having trouble
> > with.
> > INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> > ScanID, AttributeValue, DateCreated, LastModified)
> > SELECT @.MAC, #temp.ID, #temp.ParentID,
> > tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> > #temp.scanattribute, DateCreated = getdate(),
> > LastModified =
> > getdate()
> > FROM tblScan, tblScanAttribute JOIN #temp ON
> > tblScanAttribute.Name =
> > #temp.Name
> > If there is a way to do this without the temporary table that would be
> > great, but I haven't figured a way around it yet, if anyone has any
> > ideas that would be great, thanks.
> I have some difficulties to understand what your problem is. If all
> you want to do is to insert from the XML document, then you don't
> need the temp table, but you could use OPENXML directly in the
> query.
> But then you talk about an UPDATE as well, and if your aim is to insert
> new rows, and update existing, it's probably better to use a temp
> table (or a table variable), so that you don't have to run OPENXML twice.
> Some DB engines support a MERGE command which performs the task of
> UPDATE and INSERT in one statement, but this is not available in
> SQL Server, not even in SQL 2005.
> If this did not answer your question, could you please clarify?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Fixed it no problems.
rhaazy wrote:
> My app runs on all my companies PCs every month a scan is performed and
> the resulst are stored in a database. So the first time a scan is
> performed for any PC it will be an insert, but after that it will
> always be an update. I tried using openxml in my insert statement but
> kept getting an error stating my sub query is returning more than one
> result... So since I couldn't do it that way I'm trying this method.
> All the relevent openxml is there I just couldn't figure out how to
> insert each column using it. If you have any suggestions I'm open to
> give it a try.
> Erland Sommarskog wrote:
> > rhaazy (rhaazy@.gmail.com) writes:
> > > INSERT INTO #temp
> > > SELECT * FROM openxml(@.iTree,
> > > 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> > > WITH(
> > > ID nvarchar(50) './@.ID',
> > > ParentID nvarchar(50) './@.ParentID',
> > > Name nvarchar(50) './@.Name',
> > > scanattribute nvarchar(50) '.'
> > > )
> > > > Now here is the insert statement for the table I am having trouble
> > > with.
> > > > INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> > > ScanID, AttributeValue, DateCreated, LastModified)
> > > SELECT @.MAC, #temp.ID, #temp.ParentID,
> > > tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> > > #temp.scanattribute, DateCreated = getdate(),
> > > LastModified =
> > > getdate()
> > > FROM tblScan, tblScanAttribute JOIN #temp ON
> > > tblScanAttribute.Name =
> > > #temp.Name
> > > > If there is a way to do this without the temporary table that would be
> > > great, but I haven't figured a way around it yet, if anyone has any
> > > ideas that would be great, thanks.
> > I have some difficulties to understand what your problem is. If all
> > you want to do is to insert from the XML document, then you don't
> > need the temp table, but you could use OPENXML directly in the
> > query.
> > But then you talk about an UPDATE as well, and if your aim is to insert
> > new rows, and update existing, it's probably better to use a temp
> > table (or a table variable), so that you don't have to run OPENXML twice.
> > Some DB engines support a MERGE command which performs the task of
> > UPDATE and INSERT in one statement, but this is not available in
> > SQL Server, not even in SQL 2005.
> > If this did not answer your question, could you please clarify?
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/pr...oads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Have a better UPDATE statement than what I wrote?

/*
This is a long post. You can paste the whole message
in the SQL Query Analyzer.
I have a scenario where there are records
with values pointing to wrong records and I need to fix them
using an Update statement.

I have a sample code to reproduce my problem.
To simplify the scenario I am trying to use Order related
tables to explain a little better the tables i have to work with.
Please don't bother looking at the wrong relationship and how
the tables are designed. That's not my current problem. My
job is to correct the wrong data either using code or manually.

Here are the tables I have created:

TBLORDERS where two fields I am interested in are:
ORDERTYPENO linking to TBLORDERTYPE
LASTSTATUSNO linking to TBLSTATUS

TBLORDERTYPE where one field I am interested in is
ORDERPROCESSINGNO

TBLORDERPROCESSING

Each order has a link to OrderTypeNo and each
OrderTypeNo has a link to OrderProcessingNo.

TBLORDERSTATUSES where one field I am
interested in is
STATUSNO

TBLSTATUS where one field I am interested in is
ORDERPROCESSINGNO

I have the sample code here:
*/

--DROP DATABASE TestDB

CREATE DATABASE TestDB
GO

USE TestDB

CREATE TABLE TBLORDER
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERNUMBER VARCHAR(50),
ORDERTYPENO INT,
LASTSTATUSNO INT
)

INSERT INTO TBLORDER (IDNO, ORDERNUMBER, ORDERTYPENO, LASTSTATUSNO)
SELECT 1, 'ORDERTEST1', 1, 3 UNION ALL
SELECT 2, 'ORDERTEST2', 1, 3 UNION ALL
SELECT 3, 'ORDERTEST3', 2, 16 UNION ALL
SELECT 4, 'ORDERTEST4', 2, 16 UNION ALL
SELECT 5, 'ORDERTEST5', 2, 16 UNION ALL
SELECT 6, 'ORDERTEST6', 2, 16 UNION ALL
SELECT 7, 'ORDERTEST7', 4, 5 UNION ALL
SELECT 8, 'ORDERTEST8', 4, 5 UNION ALL
SELECT 9, 'ORDERTEST9', 6, 22 UNION ALL
SELECT 10, 'ORDERTEST10', 6, 22 UNION ALL
SELECT 11, 'ORDERTEST11', 7, 20

CREATE TABLE TBLORDERSTATUSES
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERNO INT,
STATUSNO INT
)

INSERT INTO TBLORDERSTATUSES (IDNO, ORDERNO, STATUSNO)
SELECT 1, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 3, 1, 3 UNION ALL
SELECT 4, 1, 4 UNION ALL
SELECT 5, 2, 1 UNION ALL
SELECT 6, 2, 2 UNION ALL
SELECT 7, 2, 3 UNION ALL
SELECT 8, 2, 4 UNION ALL
SELECT 9, 3, 15 UNION ALL
SELECT 10, 3, 16 UNION ALL
SELECT 11, 3, 17 UNION ALL
SELECT 12, 4, 15 UNION ALL
SELECT 13, 4, 16 UNION ALL
SELECT 14, 4, 17 UNION ALL
SELECT 15, 5, 15 UNION ALL
SELECT 16, 5, 16 UNION ALL
SELECT 17, 5, 17 UNION ALL
SELECT 18, 6, 15 UNION ALL
SELECT 19, 6, 16 UNION ALL
SELECT 20, 6, 17 UNION ALL
SELECT 21, 7, 5 UNION ALL
SELECT 22, 7, 6 UNION ALL
SELECT 23, 8, 5 UNION ALL
SELECT 24, 8, 6 UNION ALL
SELECT 25, 9, 22 UNION ALL
SELECT 26, 9, 23 UNION ALL
SELECT 27, 9, 24 UNION ALL
SELECT 28, 9, 25 UNION ALL
SELECT 29, 10, 22 UNION ALL
SELECT 30, 10, 23 UNION ALL
SELECT 31, 10, 24 UNION ALL
SELECT 32, 10, 25 UNION ALL
SELECT 33, 11, 18 UNION ALL
SELECT 34, 11, 19 UNION ALL
SELECT 35, 11, 20 UNION ALL
SELECT 36, 11, 21

CREATE TABLE TBLORDERTYPE
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERTYPE VARCHAR(50),
ORDERPROCESSINGNO INT
)

INSERT INTO TBLORDERTYPE (IDNO, ORDERTYPE, ORDERPROCESSINGNO)
SELECT 1, 'CATEGORY 100', 1 UNION ALL
SELECT 2, 'CATEGORY 200', 5 UNION ALL
SELECT 3, 'CATEGORY 300', 3 UNION ALL
SELECT 4, 'CATEGORY 400', 2 UNION ALL
SELECT 5, 'CATEGORY 500', 4 UNION ALL
SELECT 6, 'CATEGORY 600', 9 UNION ALL
SELECT 7, 'CATEGORY 700', 8 UNION ALL
SELECT 8, 'CATEGORY 800', 7 UNION ALL
SELECT 9, 'CATEGORY 900', 6

CREATE TABLE TBLORDERPROCESSING
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERPROCESSING VARCHAR(50)
)

INSERT INTO TBLORDERPROCESSING (IDNO, ORDERPROCESSING)
SELECT 1, 'ORDER PROCESSING A1' UNION ALL
SELECT 2, 'ORDER PROCESSING A9' UNION ALL
SELECT 3, 'ORDER PROCESSING Z5' UNION ALL
SELECT 4, 'ORDER PROCESSING 76' UNION ALL
SELECT 5, 'ORDER PROCESSING 98' UNION ALL
SELECT 6, 'ORDER PROCESSING AB' UNION ALL
SELECT 7, 'ORDER PROCESSING 11' UNION ALL
SELECT 8, 'ORDER PROCESSING T7' UNION ALL
SELECT 9, 'ORDER PROCESSING ZX'

CREATE TABLE TBLSTATUS
(
IDNO INT PRIMARY KEY NOT NULL,
STATUS VARCHAR(50),
ORDERPROCESSINGNO INT
)

INSERT INTO TBLSTATUS (IDNO, STATUS, ORDERPROCESSINGNO)
SELECT 1, 'ABC', 1 UNION ALL
SELECT 2, 'DEF', 1 UNION ALL
SELECT 3, 'GHI', 1 UNION ALL
SELECT 4, 'JKL', 1 UNION ALL
SELECT 5, 'MNO', 2 UNION ALL
SELECT 6, 'PQR', 2 UNION ALL
SELECT 7, 'STU', 3 UNION ALL
SELECT 8, 'VWX', 3 UNION ALL
SELECT 9, 'YZ', 3 UNION ALL
SELECT 10, '123', 3 UNION ALL
SELECT 11, '456', 3 UNION ALL
SELECT 12, '789', 3 UNION ALL
SELECT 13, '0AA', 3 UNION ALL
SELECT 14, '0BB', 3 UNION ALL
SELECT 15, '0CC', 5 UNION ALL
SELECT 16, '0DD', 5 UNION ALL
SELECT 17, '0EE', 5 UNION ALL
SELECT 18, '0FF', 8 UNION ALL
SELECT 19, '0GG', 8 UNION ALL
SELECT 20, '0HH', 8 UNION ALL
SELECT 21, '0II', 8 UNION ALL
SELECT 22, '0JJ', 9 UNION ALL
SELECT 23, '0KK', 9 UNION ALL
SELECT 24, '0LL', 9 UNION ALL
SELECT 25, '0MM', 9

/*
If you run the above, the data is CORRECT and the way
it normally should be.

Basically, each Order is linked to an OrderTypeNo. Each
OrderTypeNo is linked to an OrderProcessingNo.
Each Order has MANY OrderStatuses. Each
OrderProcessingNo has MANY Statuses.
So both TBLORDERTYPE and TBLSTATUS is pointing
to TBLORDERPROCESSING. I will mess up an Order
record for example to point to a wrong OrderType and
leave its LASTSTATUSNO and all its CHILD
TBLORDERSTATUSES STATUS records point to
the CORRECT ORDERPROCESSINGNO.
*/

UPDATE TBLORDER
SET ORDERTYPENO = 3
WHERE IDNO = 5 OR IDNO = 10

/*
So now both Order IDNO 5 & 10 are basically messed
up as they are pointing to ORDERTYPENO 3 (i.e.
ORDERPROCESSINGNO 3) whereas their
LASTSTATUSNO and all its TBLORDERSTATUS
STATUS records are pointing to ......
ORDERPROCESSINGNO 5 & 9

Now I will mess up both TBLORDER and
TBLORDERDETAILS in order for my code
NOT to fix it since this I will have to deal and
decide what to correct manually.
*/

UPDATE TBLORDER
SET ORDERTYPENO = 2, LASTSTATUSNO = 15
WHERE IDNO = 8

SELECT * FROM TBLORDER
GO

CREATE VIEW VIEW1
-- This VIEW1 returns all TBLORDER records that have the problem
AS
SELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNO
FROM TBLORDER
INNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNO
INNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNO
AND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNO
GO

CREATE VIEW VIEW2
-- This VIEW2 does a GROUP BY of all TBLORDER.IDNO &
TBLSTATUS.ORDERPROCESSINGNO
AS
SELECT TOP 100 PERCENT TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
FROM TBLORDERSTATUSES INNER JOIN
TBLORDER ON TBLORDERSTATUSES.ORDERNO = TBLORDER.IDNO
INNER JOIN
TBLSTATUS ON TBLORDERSTATUSES.STATUSNO =
TBLSTATUS.IDNO INNER JOIN
VIEW1 ON TBLORDER.IDNO = VIEW1.IDNO
GROUP BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
ORDER BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
GO

CREATE VIEW VIEW3
-- This VIEW3 checks to see if TBLORDERSTATUS records have more than one
ORDERPROCESSINGNO
AS
SELECT IDNO
FROM VIEW2
GROUP BY IDNO
HAVING (COUNT(*) > 1)
GO

CREATE TABLE TMPORDERS
(
IDNO INT,
OLDORDERTYPENO INT,
NEWORDERTYPENO INT,
LASTSTATUSNO INT
)

INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
SELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNO
FROM TBLORDER
INNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNO
INNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNO
AND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNO
LEFT JOIN VIEW3 ON TBLORDER.IDNO = VIEW3.IDNO AND VIEW3.IDNO IS NULL

SELECT * FROM TMPORDERS

UPDATE TMPORDERS
SET NEWORDERTYPENO = TBLORDERTYPE.IDNO
FROM TBLORDERTYPE
INNER JOIN TBLORDERPROCESSING ON TBLORDERTYPE.ORDERPROCESSINGNO =
TBLORDERPROCESSING.IDNO
INNER JOIN TBLSTATUS ON TBLORDERPROCESSING.IDNO =
TBLSTATUS.ORDERPROCESSINGNO
WHERE TBLSTATUS.IDNO = TMPORDERS.LASTSTATUSNO

UPDATE TBLORDER
SET ORDERTYPENO = NEWORDERTYPENO
FROM TMPORDERS
WHERE TMPORDERS.IDNO = TBLORDER.IDNO

SELECT * FROM TBLORDER

/*
Is there a better to write my Update statement? As you can see that
I am using 3 views, 1 temp table and 2 update statements to
fix my problem.
I am not even sure if i'll need to add more update statements
to handle other corrections. If that is the case I am trying to
see if my code can be simplified in order for it to be easily
modifiable to handle other scenarios.

Thank you for your time.
*/Hello, Serge

1. To complete your DDL, you should also add the foreign keys and
unique constraints:

ALTER TABLE TBLORDERTYPE ADD FOREIGN KEY (ORDERPROCESSINGNO)
REFERENCES TBLORDERPROCESSING (IDNO)
ALTER TABLE TBLSTATUS ADD FOREIGN KEY (ORDERPROCESSINGNO)
REFERENCES TBLORDERPROCESSING (IDNO)
ALTER TABLE TBLORDER ADD FOREIGN KEY (ORDERTYPENO)
REFERENCES TBLORDERTYPE (IDNO)
ALTER TABLE TBLORDER ADD FOREIGN KEY (LASTSTATUSNO)
REFERENCES TBLSTATUS (IDNO)
ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (ORDERNO)
REFERENCES TBLORDER (IDNO)
ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (STATUSNO)
REFERENCES TBLSTATUS (IDNO)

ALTER TABLE TBLSTATUS ADD UNIQUE (STATUS)
ALTER TABLE TBLORDERTYPE ADD UNIQUE (ORDERTYPE)
ALTER TABLE TBLORDERPROCESSING ADD UNIQUE (ORDERPROCESSING)
ALTER TABLE TBLORDER ADD UNIQUE (ORDERNUMBER)
ALTER TABLE TBLORDERSTATUSES ADD UNIQUE (ORDERNO, STATUSNO)

2. Your "INSERT INTO TMPORDERS [...]" does not perform as you expect,
because the condition "AND VIEW3.IDNO IS NULL" is in the "LEFT JOIN"
clause, not in the WHERE clause (and therefore it's ignored). To
exclude from the INSERT any rows that are in VIEW3 you need to move the
condition "VIEW3.IDNO IS NULL" to the WHERE clause.

3. This corrected "INSERT INTO TMPORDERS [...]" can be rewritten
(without using views and with a slight performance improvement) as:

INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
SELECT O1.IDNO, O1.ORDERTYPENO, O1.LASTSTATUSNO
FROM TBLORDER O1
INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
AND NOT EXISTS (
SELECT O2.IDNO
FROM TBLORDER O2
INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
WHERE O2.IDNO IN (
SELECT O3.IDNO
FROM TBLORDER O3
INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
)
GROUP BY O2.IDNO
HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
)

4. The "UPDATE TMPORDERS" statement, can be rewritten (by eliminating
the join with the TBLORDERPROCESSING) as:

UPDATE TMPORDERS SET NEWORDERTYPENO = T.IDNO
FROM TBLORDERTYPE T INNER JOIN TBLSTATUS S
ON T.ORDERPROCESSINGNO = S.ORDERPROCESSINGNO
WHERE S.IDNO = TMPORDERS.LASTSTATUSNO

5. The whole story can be written in a single UPDATE statement, like
this:

UPDATE TBLORDER SET ORDERTYPENO = NEWORDERTYPENO
FROM TBLORDER O INNER JOIN (
SELECT O1.IDNO, (
SELECT T4.IDNO FROM TBLORDERTYPE T4
INNER JOIN TBLSTATUS S4
ON T4.ORDERPROCESSINGNO = S4.ORDERPROCESSINGNO
WHERE S4.IDNO = O1.LASTSTATUSNO
) AS NEWORDERTYPENO
FROM TBLORDER O1
INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
AND NOT EXISTS (
SELECT O2.IDNO
FROM TBLORDER O2
INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
WHERE O2.IDNO IN (
SELECT O3.IDNO
FROM TBLORDER O3
INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
)
GROUP BY O2.IDNO
HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
)
) X ON O.IDNO=X.IDNO

I have to admit that this is a rather complex UPDATE statement and,
while it's performance is better than your solution, maintainability
may be less. So you may prefer using some views to improve readability
(for example VIEW1, which can be used in two places in the above
statement). However, there are some interesting points that you can
learn from this complex statement:
a) avoiding the use of temporary tables, when subqueries can be used
b) the use of table aliases, to improve readability
c) the use of "COUNT(DISTINCT something)" instead of two "GROUP BY"-s;
d) the use of "NOT EXISTS" subqueries, instead of "LEFT JOIN ... WHERE
... IS NULL";
e) the use of "WHERE ... IN" subqueries, instead of joins (when there
is no column used from the joined subquery);
f) the use of subqueries in the SELECT clause, instead of joins (when
there is only one column used from the joined subquery).
I think that these usages of subqueries (points d,e,f) improve
readability (and may, in rare cases, even improve performance), but
that's for you to decide, in each particular case.

Razvan|||Hello Razvan,

Thank you for your detailed explanation.

It will take me a little bit of time to go through the points
and understand them.

Thanks again.

> 1. To complete your DDL, you should also add the foreign keys and
> unique constraints:
> ALTER TABLE TBLORDERTYPE ADD FOREIGN KEY (ORDERPROCESSINGNO)
> REFERENCES TBLORDERPROCESSING (IDNO)
> ALTER TABLE TBLSTATUS ADD FOREIGN KEY (ORDERPROCESSINGNO)
> REFERENCES TBLORDERPROCESSING (IDNO)
> ALTER TABLE TBLORDER ADD FOREIGN KEY (ORDERTYPENO)
> REFERENCES TBLORDERTYPE (IDNO)
> ALTER TABLE TBLORDER ADD FOREIGN KEY (LASTSTATUSNO)
> REFERENCES TBLSTATUS (IDNO)
> ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (ORDERNO)
> REFERENCES TBLORDER (IDNO)
> ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (STATUSNO)
> REFERENCES TBLSTATUS (IDNO)
> ALTER TABLE TBLSTATUS ADD UNIQUE (STATUS)
> ALTER TABLE TBLORDERTYPE ADD UNIQUE (ORDERTYPE)
> ALTER TABLE TBLORDERPROCESSING ADD UNIQUE (ORDERPROCESSING)
> ALTER TABLE TBLORDER ADD UNIQUE (ORDERNUMBER)
> ALTER TABLE TBLORDERSTATUSES ADD UNIQUE (ORDERNO, STATUSNO)
> 2. Your "INSERT INTO TMPORDERS [...]" does not perform as you expect,
> because the condition "AND VIEW3.IDNO IS NULL" is in the "LEFT JOIN"
> clause, not in the WHERE clause (and therefore it's ignored). To
> exclude from the INSERT any rows that are in VIEW3 you need to move the
> condition "VIEW3.IDNO IS NULL" to the WHERE clause.
> 3. This corrected "INSERT INTO TMPORDERS [...]" can be rewritten
> (without using views and with a slight performance improvement) as:
> INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
> SELECT O1.IDNO, O1.ORDERTYPENO, O1.LASTSTATUSNO
> FROM TBLORDER O1
> INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
> INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
> WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
> AND NOT EXISTS (
> SELECT O2.IDNO
> FROM TBLORDER O2
> INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
> INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
> WHERE O2.IDNO IN (
> SELECT O3.IDNO
> FROM TBLORDER O3
> INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
> INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
> WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
> )
> GROUP BY O2.IDNO
> HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
> )
> 4. The "UPDATE TMPORDERS" statement, can be rewritten (by eliminating
> the join with the TBLORDERPROCESSING) as:
> UPDATE TMPORDERS SET NEWORDERTYPENO = T.IDNO
> FROM TBLORDERTYPE T INNER JOIN TBLSTATUS S
> ON T.ORDERPROCESSINGNO = S.ORDERPROCESSINGNO
> WHERE S.IDNO = TMPORDERS.LASTSTATUSNO
> 5. The whole story can be written in a single UPDATE statement, like
> this:
> UPDATE TBLORDER SET ORDERTYPENO = NEWORDERTYPENO
> FROM TBLORDER O INNER JOIN (
> SELECT O1.IDNO, (
> SELECT T4.IDNO FROM TBLORDERTYPE T4
> INNER JOIN TBLSTATUS S4
> ON T4.ORDERPROCESSINGNO = S4.ORDERPROCESSINGNO
> WHERE S4.IDNO = O1.LASTSTATUSNO
> ) AS NEWORDERTYPENO
> FROM TBLORDER O1
> INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
> INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
> WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
> AND NOT EXISTS (
> SELECT O2.IDNO
> FROM TBLORDER O2
> INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
> INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
> WHERE O2.IDNO IN (
> SELECT O3.IDNO
> FROM TBLORDER O3
> INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
> INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
> WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
> )
> GROUP BY O2.IDNO
> HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
> )
> ) X ON O.IDNO=X.IDNO
> I have to admit that this is a rather complex UPDATE statement and,
> while it's performance is better than your solution, maintainability
> may be less. So you may prefer using some views to improve readability
> (for example VIEW1, which can be used in two places in the above
> statement). However, there are some interesting points that you can
> learn from this complex statement:
> a) avoiding the use of temporary tables, when subqueries can be used
> b) the use of table aliases, to improve readability
> c) the use of "COUNT(DISTINCT something)" instead of two "GROUP BY"-s;
> d) the use of "NOT EXISTS" subqueries, instead of "LEFT JOIN ... WHERE
> ... IS NULL";
> e) the use of "WHERE ... IN" subqueries, instead of joins (when there
> is no column used from the joined subquery);
> f) the use of subqueries in the SELECT clause, instead of joins (when
> there is only one column used from the joined subquery).
> I think that these usages of subqueries (points d,e,f) improve
> readability (and may, in rare cases, even improve performance), but
> that's for you to decide, in each particular case.
> Razvan