Home » SQL & PL/SQL » SQL & PL/SQL » Help in refing the Sql..
Help in refing the Sql.. [message #279064] Tue, 06 November 2007 15:54 Go to next message
anilreddy76
Messages: 9
Registered: September 2007
Junior Member
Hi,

I've 2 tables "Test1" and Test2"

Test1 Table contains 3 columns

id,
Location,
Vendor,
Input_date

Test2 Table contains same 3 columns

Location,
Vendor,
Input_date.

I want to insert/update the records in Test1 table based on the values from "Test2" table.

I want to check whether new locations or vendors are present in "Test2" table before
doing insert into "Test1" table and also to see whether for particular locations
in "Test2" table whether there are any new vendors and insert the same into "Test1" table.

For update I need to check if there are any updates with respect to the vendors for particular location.

I've written the following Sql's for Insert/Update,but I think it is not meeting my requirements.

Insert into Test1(id,location,vendor,INPUT_DATE)
select vendor_sequence.nextval,t.* from (select DISTINCT(location), vendor, sysdate from Test2 where
location not in(select location from test1) and vendor not in(select location from test1))t;

Update Test1 vn set(location,vendor,INPUT_DATE)
=
(select DISTINCT(location),vendor,SYSDATE from Test2 sdata where vn.Vendor= sdata.vendor and vn.location = sdata.location);

Please advise me on this.

Thanks,
Anil
Re: Help in refing the Sql.. [message #279071 is a reply to message #279064] Tue, 06 November 2007 17:16 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Anil please format your post first. See orafaq forum guide for formatting.

Better to use merge statement instead of inseting this type.


Cheers
Soumen
Re: Help in refing the Sql.. [message #279237 is a reply to message #279071] Wed, 07 November 2007 07:56 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Soumen Kamilya wrote on Tue, 06 November 2007 18:16

Anil please format your post first. See orafaq forum guide for formatting.



Yeah right, you'd have a better chance of learning how to fly than getting this user to use code tags (ie. look at previous posts by this user).
Re: Help in refing the Sql.. [message #279245 is a reply to message #279237] Wed, 07 November 2007 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Let him not formatting...
and let him without answer.

Regards
Michel
Re: Help in refing the Sql.. [message #279253 is a reply to message #279245] Wed, 07 November 2007 08:43 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Here is the formatted code:
INSERT INTO test1
  (id
  ,location
  ,vendor
  ,input_date)
  SELECT vendor_sequence.NEXTVAL
        ,t.*
  FROM   (SELECT DISTINCT (location)
                         ,vendor
                         ,SYSDATE
          FROM   test2
          WHERE  location NOT IN (SELECT location
                                  FROM   test1)
          AND    vendor NOT IN (SELECT location
                                FROM   test1)) t;
                                
UPDATE test1 vn
SET    (location, vendor, input_date) = (SELECT DISTINCT (location)
                                                        ,vendor
                                                        ,SYSDATE
                                         FROM   test2 sdata
                                         WHERE  vn.vendor = sdata.vendor
                                         AND    vn.location = sdata.location);

Anil, next time do your own formatting please.
Michel, Joy, to be honest I think you're being a bit childish now. It's not like he has posted 50 posts without formatting...

Back on-topic...
One thing I noticed:
AND vendor NOT IN (SELECT location FROM   test1)

Chances are that vendor will not be equal to location Wink

Re: Help in refing the Sql.. [message #279255 is a reply to message #279253] Wed, 07 November 2007 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Michel, Joy, to be honest I think you're being a bit childish now. It's not like he has posted 50 posts without formatting...

It's a life behaviour.
How many times you have to repeat someone he has to follow the rules?

Now it's up to you to don't take into account what we say.
In my opinion, in this case, we will not see a formatted post from him before quite a long time.

Regards
Michel


Re: Help in refing the Sql.. [message #279259 is a reply to message #279255] Wed, 07 November 2007 09:15 Go to previous messageGo to next message
anilreddy76
Messages: 9
Registered: September 2007
Junior Member
I'm extremely sorry for not posting in proper format.

I'll do it from the next post.

Thanks,
Anil
Re: Help in refing the Sql.. [message #279263 is a reply to message #279255] Wed, 07 November 2007 09:21 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Michel, did I miss something here? He has made 6 posts so far, right? Is there a certain limit in number of posts after which one is supposed to know about formatting and one doesn't get answers anymore? No offense, but I think that 6 posts (including replies) is a bit strict.
Re: Help in refing the Sql.. [message #279326 is a reply to message #279064] Wed, 07 November 2007 16:15 Go to previous messageGo to next message
anilreddy76
Messages: 9
Registered: September 2007
Junior Member
Hi,

I got the solution for this,no need to worry about it.

Next time onwards I'll post the message in proper formatting.

Thanks,
Anil
Re: Help in refing the Sql.. [message #279380 is a reply to message #279263] Thu, 08 November 2007 00:46 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sabine,

1... or to behave generously 2. Laughing

Regards
Michel
Previous Topic: Index rebuild
Next Topic: Dynamic PIVOT
Goto Forum:
  


Current Time: Sat Dec 10 03:13:37 CST 2016

Total time taken to generate the page: 0.08544 seconds