Home » SQL & PL/SQL » SQL & PL/SQL » transferring records
transferring records [message #205930] Tue, 28 November 2006 03:10 Go to next message
webadministrator
Messages: 45
Registered: October 2005
Member
hello
i have 15 records in a table corresponding to a same company having the code_company = 1. i want to transfer them to company having code_company = 2, how can i do it automatically?

thank you.
Re: transferring records [message #205933 is a reply to message #205930] Tue, 28 November 2006 03:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
UPDATE table
SET code_company = 2 
WHERE code_company = 1
Re: transferring records [message #205936 is a reply to message #205930] Tue, 28 November 2006 03:28 Go to previous messageGo to next message
webadministrator
Messages: 45
Registered: October 2005
Member
i want to keep the records in code_company =1
i just wanna copy them to the second company.
Re: transferring records [message #205938 is a reply to message #205936] Tue, 28 November 2006 03:32 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
INSERT INTO your_table (code_company, name, ...)
  (SELECT 2, name, ...
   FROM your_table
   WHERE code_company = 1
  );
Re: transferring records [message #206036 is a reply to message #205930] Tue, 28 November 2006 07:01 Go to previous messageGo to next message
webadministrator
Messages: 45
Registered: October 2005
Member
sorry to bother. im trying the query, im getting a unique constraint key violated. because one of the field is the primary key.
isnt pl/sql supposed to know how to increment the key? if not what shall i do ?

Re: transferring records [message #206039 is a reply to message #206036] Tue, 28 November 2006 07:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If theres a trigger on the table to populate the primary key, then yes, it should auto-populate the PK.
It's possible the trigger has been badly written and will only give you a new PK if the record you are trying to insert has no PK.

If there's no trigger, then you'll need to look in the application and see where these records are normally added from, and copy how they generate PK values.
Re: transferring records [message #206050 is a reply to message #205930] Tue, 28 November 2006 07:43 Go to previous message
webadministrator
Messages: 45
Registered: October 2005
Member
thank u its solved.

i have a sequence corresponding to this table so i just put in the query:
...
(select seqcomp.nextval, name....)

and it worked Smile
Previous Topic: converting long raw into blob in a procedure over a database-link
Next Topic: utl_file halts
Goto Forum:
  


Current Time: Fri Dec 02 14:10:16 CST 2016

Total time taken to generate the page: 0.13700 seconds