Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> HIVE NOT EXISTS


+
Mohit Anchlia 2012-10-01, 20:42
Copy link to this message
-
Re: HIVE NOT EXISTS
Hi Mohit,

Hive doesn’t support correlated subqueries.  In this instance, you can do a
left outer join to find values that are not in a table.

SELECT

    “”,

    a.pagename,

    a.pagedetail,

    “”,

    a.pagetitle,

    a.page_id,

    a.pagetype

FROM page_temp_ext a

    LEFT OUTER JOIN page_temp b ON

        a.pagename = b.pagename AND

        a.pagetitle = b.pagetitle AND

        a.page_id = b.pageid AND

        a.pagetype = b.pagetype

WHERE

    b.pagename IS NULL
Hope that helps,

Matt
       On Mon, Oct 1, 2012 at 4:42 PM, Mohit Anchlia <[EMAIL PROTECTED]
> wrote:

> Could someone help me understand what alternative do I have for this
> query? I am trying to check if a given row exists in the table.
>
> select "",a.pagename,a.pagedetail,"",a.pagetitle,a.page_id,a.pagetype
>   from page_temp_ext a
> where 0 = (select count(*) from page_temp b where a.pagename = b.pagename
> and a.pagetitle = b.pagetitle and a.page_id = b.pageid and a.pagetype > b.pagetype);
>
+
Mohit Anchlia 2012-10-01, 22:59
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB