Keyword WHERE not expected error

I am getting keyword WHERE not expected error
update  file1  set (file1.fld1, file1.fld2) =            
                                                    
   (select  file2.fld3, file3.max(fld4)                     
      from file2                                  
   left join  file3  on  file3.key1  = file2.key1     
     and file3.key2  = file2.key2                             
                                                    
GROUP BY  file3.key1, file3.key2                            
     where file1.keya = file2.keya   and file1.keyb = file2.keyb)   
    where  file1.keya =  'xxx'  and file1.keyb = 1

Open in new window

RozamundaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Out of order maybe?
update  file1  set (file1.fld1, file1.fld2) =            
                                                    
   (select  file2.fld3, file3.max(fld4)                     
      from file2                                  
   left join  file3  on  file3.key1  = file2.key1     
     and file3.key2  = file2.key2                             
   where file1.keya = file2.keya   and file1.keyb = file2.keyb
GROUP BY  file3.key1, file3.key2 )   
    where  file1.keya =  'xxx'  and file1.keyb = 1

Open in new window

0
RozamundaAuthor Commented:
No because Group by pertains to File3 (the left join) and where is for updating file1 with file2
0
RozamundaAuthor Commented:
the last where will be replace later by where exists...to do a massive update, so I kept it for later
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Kent OlsenData Warehouse Architect / DBACommented:
Hi Rozamunda,

This query looks to be more complicated than it needs to be.  :)

The GROUP BY clause needs to be the scalar items from the select clause.  The query groups by file3.key1, file3.key2.

However, the query joins on keya and keyb.  These aren't selected in the inner sub-query.

Apparently, this query is "paraphrased" from a real world example.  If you can post the real one, or explain a bit better what you're trying to do, I'll be glad to help you work through it.


Kent
0
RozamundaAuthor Commented:
ok, this how I update my main file1 with file2, by keya and keyb

Since file2 does not have all the filelds I join it with file3.
It works perfectly fine without group by, and I join tens of files, the query is very long so I just
decided to reduce the problem to its core, I need to to group by because file3 has an additional key whcih I don't have on file2
0
Dave FordSoftware Developer / Database AdministratorCommented:
Essentially, it's complaining because your WHERE clause needs to come before your GROUP BY clause.

Try something like this:

update file1
   set (file1.fld1, file1.fld2) = (
          select file2.fld3, file3.max(fld4)
            from file2
            left outer join file3
              on file3.key1 = file2.key1
             and file3.key2 = file2.key2
           where file1.keya = file2.keya
             and file1.keyb = file2.keyb
           GROUP BY file3.key1,
                 file3.key2)
 where file1.keya = 'xxx'
   and file1.keyb = 1 

HTH,
DaveSlash

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.