Update and Count question

[kiblinger]

I know I have the syntax totally wrong.

I want to do something like:

UPDATE Flag = 1
FROM Dataset WHERE
("Label" = 'XYZ' AND
COUNT ("Type" = 'A' OR
"Type" = 'B' OR
"Type" = 'C' OR) >= 2)

What I want, conceptually:

1. Select all records with Label = XYZ.
2. Count how many of those selected records have Type = A or B or C.
3. If that is 2 or more, update the Flag field with a '1' for all the records where Label = XYZ.

 

[gmmastros]

As always... with an update statement, you should make sure you have a good backup of your database before running this.

Untested...

[COLOR=blue]Update[/color] DataSet
[COLOR=blue]Set[/color]    Flag = 1
[COLOR=blue]From[/color]   DataSet
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]Select[/color] Label,
                [COLOR=#FF00FF]Count[/color](*)
         [COLOR=blue]From[/color]   DataSet
         [COLOR=blue]Where[/color]  [COLOR=blue]Type[/color] In ([COLOR=red]'A'[/color], [COLOR=red]'B'[/color], [COLOR=red]'C'[/color])
         [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Label
         [COLOR=blue]Having[/color] [COLOR=#FF00FF]Count[/color](*) >= 2) [COLOR=blue]As[/color] A
         [COLOR=blue]On[/color] DataSet.Label = A.Label

If this works, and you would like me to explain any part of it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Of course... minutes after posting... I discover a problem. When using a derived table, each column must be aliased (even if you don't use it outside of the derived table).

[COLOR=blue]Update[/color] DataSet
[COLOR=blue]Set[/color]    Flag = 1
[COLOR=blue]From[/color]   DataSet
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]Select[/color] Label,
                [COLOR=#FF00FF]Count[/color](*) [COLOR=blue]As[/color] RecordCount
         [COLOR=blue]From[/color]   DataSet
         [COLOR=blue]Where[/color]  [COLOR=blue]Type[/color] In ([COLOR=red]'A'[/color], [COLOR=red]'B'[/color], [COLOR=red]'C'[/color])
         [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Label
         [COLOR=blue]Having[/color] [COLOR=#FF00FF]Count[/color](*) >= 2) [COLOR=blue]As[/color] A
         [COLOR=blue]On[/color] DataSet.Label = A.Label

And, technically, you don't need that column at all, so this should also work.

[COLOR=blue]Update[/color] DataSet
[COLOR=blue]Set[/color]    Flag = 1
[COLOR=blue]From[/color]   DataSet
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]Select[/color] Label
         [COLOR=blue]From[/color]   DataSet
         [COLOR=blue]Where[/color]  [COLOR=blue]Type[/color] In ([COLOR=red]'A'[/color], [COLOR=red]'B'[/color], [COLOR=red]'C'[/color])
         [COLOR=blue]Group[/color] [COLOR=blue]By[/color] Label
         [COLOR=blue]Having[/color] [COLOR=#FF00FF]Count[/color](*) >= 2) [COLOR=blue]As[/color] A
         [COLOR=blue]On[/color] DataSet.Label = A.Label

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kiblinger]

Wow! Cool, and fast!

I think it makes sense to me (I am very inexperienced), except for the

"As A" and "A.Label"

parts. What are they doing? Is "A" one of the Type values?

 

[kiblinger]

Also, now that I look at it longer, where is it selecting the records with Label = XYZ?

Thanks again,

ck

 

[gmmastros]

This query uses a derived table. When you use a derived table, you MUST give it an alias. That's what the As A part is doing. Also... we are joining the table with the derived table.

[tt][blue]On DataSet.Label = A.Label[/blue][/tt]

Essentially, we are saying, "Only when the label column of the Data set table matches the label column of the 'A' derived table".

Make sense?

>> Also, now that I look at it longer, where is it selecting the records with Label = XYZ?

Yup. I missed that part.

Update DataSet
Set    Flag = 1
From   DataSet
       Inner Join (
         Select Label
         From   DataSet
         Where  [!]Label='XYZ'
                And [/!]Type In ('A', 'B', 'C')
         Group By Label
         Having Count(*) >= 2) As A
         On DataSet.Label = A.Label

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kiblinger]

Aha - so "A" could just be any alias I make up - "temp" or "PDQ" etc.

And I see now that you put the "XYZ" part where I thought it would go, so that means I must be learning something.

So, that is probably going to do what I need. Now I'm not at work any more so it'll wait until Monday....

Thanks again for the help and I'll report back then!

 

[kiblinger]

I should have mentioned - I am trying to do this in MS Access -- so when I tried:

Update pugetsound
Set EPU_Flag = 1
From pugetsound
Inner Join (
Select PLACE
From pugetsound
Where PLACE = '01' AND XWALK In ('ESS', 'RSS', 'REM', 'EEM', 'EEM_LOW', 'LAG-O', 'LAG-C', 'LAG/EEM-O', 'LAG/EEM-C', 'INT')
Group By PLACE
Having Count(*) >= 2) As temp
On pugetsound.PLACE = temp.PLACE

I got a "syntax error (missing operator)"

Many thanks!

 

[SQLDenis]

I should have mentioned - I am trying to do this in MS Access -- so when I tried:

yes you should have!

try here

http://www.tek-tips.com/threadminder.cfm?pid=701

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

I should have mentioned - I am trying to do this in MS Access

No, you should have posted in the forum Microsoft: Access Queries and JET SQL. Then no one would waste any time giving wrong answers.

Make the contents of the derived table its own query.
In the main query, replace the derived table with the name of that query.

If the XWALK list gets very long you might experiment with a join to a table of desired items, it could potentially speed things up greatly.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[kiblinger]

Many apologies - somehow got myself in the wrong forum.

 

[ESquared]

No apologies necessary! Most of what you were told applies, as long as you do the query thing I mentioned.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]