{"id":425,"date":"2014-10-13T13:05:05","date_gmt":"2014-10-13T13:05:05","guid":{"rendered":"http:\/\/mairwa.com\/wordpress\/?p=425"},"modified":"2014-10-13T13:05:05","modified_gmt":"2014-10-13T13:05:05","slug":"sql-query-updated","status":"publish","type":"post","link":"http:\/\/mairwa.com\/wordpress\/?p=425","title":{"rendered":"sql query updated"},"content":{"rendered":"

–update table with swap between two columns<\/strong>
\nupdate mmm_1
\nset id=phone,phone=id;
\n–delete duplicate value from data table and select not duplicate also<\/strong>
\nwith x as( select empid,lname,deptno,rn=ROW_NUMBER() over (partition by deptno order by deptno),rn1=dense_rank() over (partition by deptno order by deptno),
\nrn2=Rank() over ( order by deptno) from employees)
\ndelete x where x.rn>1;
\n–delete all duplicate values<\/strong>
\ndelete employees where deptno in ( select (deptno)from employees group by deptno having COUNT(deptno)>1);
\nselect * from employees;
\n–select total salary departmentvise and employee details<\/strong>
\nwith z as( select y.*,e2.empid,(e2.fname+’ ‘+e2.lname) as fullname,rn=ROW_NUMBER() over(partition by e2.deptno order by e2.deptno asc) from employees e2,( select e1.deptno,SUM(e1.sal) as sal from employees e1 group by deptno)y where y.deptno=e2.deptno )
\nselect * from z where rn=1;<\/p>\n

–select manager name in self join<\/strong><\/p>\n

select
\n(e1.fname+’ ‘+e1.lname)empname,e1.empid,e1.man_id,(e2.fname+’ ‘+e2.lname)as managname
\nfrom employees e1
\nleft join employees e2
\non e1.man_id=e2.empid
\nselect * from employees e1;<\/p>\n","protected":false},"excerpt":{"rendered":"

–update table with swap between two columns update mmm_1 set id=phone,phone=id; –delete duplicate value from data table and select not duplicate also with x as( select empid,lname,deptno,rn=ROW_NUMBER() over (partition by deptno order by deptno),rn1=dense_rank() over (partition by deptno order by deptno), rn2=Rank() over ( order by deptno) from employees) delete x where x.rn>1; –delete all duplicate values delete employees where… Read more →<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,14],"tags":[],"class_list":["post-425","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","xfolkentry"],"_links":{"self":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/425","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=425"}],"version-history":[{"count":0,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/425\/revisions"}],"wp:attachment":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=425"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=425"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=425"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}