?
Solved

Update with Having  - syntax error at having

Posted on 2005-04-15
11
Medium Priority
?
1,164 Views
Last Modified: 2008-01-09
This is perfectly okay.

    select *  
    FROM  
        stream_0414                                s        
    INNER JOIN wstream_0414                        w   /* Wenbo data only. */
    ON (    w.id_stream = s.id_stream
    )
    HAVING s.tx_trade not in (select cl.nm_imnt_conc from ccube_lon_callable cl )

I want to update from the result above. However i got syntax error at HAVING. Looks like update cannot have having.
How can i write a update statement join with other table but exclude some records exists in ccube_lon_callable table?

    update stream_0414
    set abc = 2  
    FROM  
        stream_0414                                s        
    INNER JOIN wstream_0414                        w   /* Wenbo data only. */
    ON (    w.id_stream = s.id_stream
    )
    HAVING s.tx_trade not in (select cl.nm_imnt_conc from ccube_lon_callable cl )


0
Comment
Question by:StanleyLMW
  • 5
  • 3
  • 2
10 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 2000 total points
ID: 13791115
Why don't you use the WHERE clause.  Having is only really used if you are using GROUP BY.

Leon
0
 

Author Comment

by:StanleyLMW
ID: 13796092

Inner Join cannot let me do where.
How exactly. Please write me the e.g.
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13796100
Hi StanleyLMW!

I agree with leonstryker.  No need to use Having, just simply use Where clause.

So, you can simply do it this way :

:: SELECTING RECORDS ::
-----------
Query 1
-----------
Select *
From stream_0414 s
Inner Join wstream_0414 w
  On w.id_stream = s.id_stream
Where s.tx_trade not in (Select nm_imnt_conc From ccube_lon_callable)

-----------
Query 2
-----------
Select *
From stream_0414 s
Inner Join wstream_0414 w
  On w.id_stream = s.id_stream
Where Not Exists
(
  Select nm_imnt_conc From ccube_lon_callable
  Where nm_imnt_conc = s.tx_trade
)

* Note : I would recommend to use Query 2 'coz it is more effecient compared with Query 1.

:: UPDATING RECORDS ::
Update stream_0414
Set abc = 2
From stream_0414 s
Inner Join wstream_0414 w
  On w.id_stream = s.id_stream
Where Not Exists
(
  Select nm_imnt_conc From ccube_lon_callable
  Where nm_imnt_conc = s.tx_trade
)

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:StanleyLMW
ID: 13799787
In where not exists, dont you need to define which identifier doesnt exist in the set? Otherwise does it know it is trying to compare the s.tx_trade?

so...
Where s.tx_trade Not Exists
(
  Select nm_imnt_conc From ccube_lon_callable
  Where nm_imnt_conc = s.tx_trade
)
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13800184
No, you should not need that. The exists only shows you if any records match or not match teh given criteria, which in this case is :

(
Select nm_imnt_conc From ccube_lon_callable
  Where nm_imnt_conc = s.tx_trade
)

Leon
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13803186
Hi StanleyLMW!

Yah ...  Leon is right.

So, may we know what's the status of this problem ???  Just let us know if you still encountered an error.


Regards!
eNTRANCE2002 :-)
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13840591
Hi StanleyLMW!

How come you only accept leonstryker's comment ???

I think this deserves to be splitted since I'm guiding you on the right track.  In fact, I'm providing you a query which is a possible solution to your problem.  And I think leonstryker will agree with me :-?

Hope this makes sense.


Regards!
eNTRANCE2002 :-)
0
 

Author Comment

by:StanleyLMW
ID: 13840927
How can i split the point now?
sorry.
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13840982
Hi StanleyLMW!

Splitting points is just simple.  Here's a reference :
   More than one Expert helped solve my problem. What do I do? - http://www.experts-exchange.com/help.jsp#hi69

But before that, since you have already close this one.  You are going to post a question on the Community Support - http://www.experts-exchange.com/Community_Support/ which is a request to re-open your question 'coz you are going to split the points.  The question is worth no points.

Hope this is clear for you.


Cheers!
eNTRANCE2002 :-)
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13855891
Hi StanleyLMW!

Just a sort of follow-up.  I think you haven't post in the Community Support regarding re-opening this question.  Am I right ???


Regards!
eNTRANCE2002 :-)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

LinkedIn blogging is great for networking, building up an audience, and expanding your influence as well. However, if you want to achieve these results, you need to work really hard to make your post worth liking and sharing. Here are 4 tips that ca…
Last month Marc Laliberte, WatchGuard’s Senior Threat Analyst, contributed reviewed the three major email authentication anti-phishing technology standards: SPF, DKIM, and DMARC. Learn more in part 2 of the series originally posted in Cyber Defense …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question