sql update - Updating Multiple Columns from another table - Need Oracle format -

-

i have script use in sql server need convert oracle format. can help?

    update persons p set          p.jobtitle=te.jobtitle,         p.last_name=te.last_name,         p.first_name=te.first_name,         p.dblogin_id=te.dblogin_id,         p.email_id=te.email_id,         p.userlevel=te.userlevel,         p.facility_id=te.facility_id,         p.supervisor=te.supervisor,         p.department=te.department,         p.winloginid=te.winloginid    temp_ecolab_persons te    p.person=te.person;

--from article below came following statement. still doesn't work unfortunately:

  update (select p.jobtitle, p.last_name, p.first_name, p.dblogin_id, p.email_id,         p.userlevel, p.facility_id, p.supervisor, p.department,         te.jobtitle, te.last_name, te.first_name, te.dblogin_id, te.email_id,         te.userlevel, te.facility_id, te.supervisor, te.department      persons p, temp_ecolab_persons te p.person=te.person)   set       p.jobtitle=te.jobtitle,      p.last_name=te.last_name,      p.first_name=te.first_name,      p.dblogin_id=te.dblogin_id,      p.email_id=te.email_id,      p.userlevel=te.userlevel,      p.facility_id=te.facility_id,      p.supervisor=te.supervisor,      p.department=te.department;

this how it. might not best performance, works.

merge persons_tmp pt using (      select p.person, p.job_title, p.first_name, p.last_name, p.facility_id      persons p) tmp on (pt.person = tmp.person) when matched  update set      pt.facility_id = tmp.facility_id,      pt.job_title = tmp.job_title,     pt.first_name = tmp.first_name,     pt.last_name = tmp.last_name;

the script above update information in persons_tmp table using data persons table. believe in case, want other way around. so, please make sure make necessary changes before running script.

you can add "when not matched then.... " clause above sql in case need insert new records, if not exist.


Comments

Post a Comment

Popular posts from this blog

java - Jmockit String final length method mocking Issue -

-
using jmockit 1.2 jar, trying mock string's length method getting unexpected invocation exception: failed: test java.lang.illegalstateexception: missing invocation mocked type @ point; please make sure such invocations appear after declaration of suitable mock field or parameter @ stringdemo.testa$1.<init>(testa.java:17) @ stringdemo.testa.test(testa.java:13) i using testng: @test public void test() throws exception { new expectations() { @mocked("length") string astring; { astring.length(); result = 2; } }; system.out.println(a.showa("test")); } } actual class a: public class { public static int showa(string str){ int a= str.length(); return a; } } this wrong way of recording expected results. shouldn't mock , record string's length() method, record showa() instead. here solution @suppresswarnings("unused...
Read more

What is the difference between data design and data model(ERD) -

-
i have google question , find answer in day. know, data design many many table have list column,and mark pk , fk. why er-diagram have find this? data design process of designing database. main output of data design detailed logical data model of database. some people data design includes of needed logical , physical design choices , physical storage parameters needed generate design in data definition language. technically, database analysis, database analysts (dbas) trained do. while person can both data design , database analysis, these 2 different tasks. data design models data. database analysis takes model , applies 1 or more database engines (relational, hierarchical, nosql). a logical data model 1 of main outputs of data design. data model represented entity relationship diagram, or er diagram.
Read more

ios - Can NSManagedObject conform to NSCoding -

-
i need transfer single object across device. right converting nsmanagedobject dictionary , archiving , sending nsdata. upon receiving unarchiving it. transfer nsmanagedobject archiving , unarchiving instead of creating intermediate data object. @interface test : nsmanagedobject<nscoding> @property (nonatomic, retain) nsstring * title; @end @implementation test @dynamic title; - (id)initwithcoder:(nscoder *)coder { self = [super init]; if (self) { self.title = [coder decodeobjectforkey:@"title"]; //<crash } return self; } - (void)encodewithcoder:(nscoder *)coder { [coder encodeobject:self.title forkey:@"title"]; } @end nsdata *archivedobjects = [nskeyedarchiver archiveddatawithrootobject:testobj]; nsdata *objectsdata = archivedobjects; if ([objectsdata length] > 0) { nsarray *objects = [nskeyedunarchiver unarchiveobjectwithdata:objectsdata]; } the problem above code is. crashes @ self.title in initwithcoder sa...
Read more