asked on

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

Dave Baldwin

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

Rozamunda

ASKER

No because Group by pertains to File3 (the left join) and where is for updating file1 with file2

Rozamunda

ASKER

the last where will be replace later by where exists...to do a massive update, so I kept it for later

Kent Olsen

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

Rozamunda

ASKER

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

ASKER CERTIFIED SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial