|
View previous topic :: View next topic
|
| Author |
Message |
vkisweet
New User
Joined: 28 Aug 2008
Posts: 2
Location: Bangalore
|
|
|
|
Hello,
How can I select an existing record from the table and change the primary key value, other fields unchanged and insert back as a new record or replace the existing one. |
|
| Back to top |
|
 |
anil.csk
New User
Joined: 22 Oct 2007
Posts: 16
Location: Noida
|
|
|
|
Use the query:
Insert into tablename (fields name) select fields names from table where primary = anything what wever you want.. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
And after the insert is successful, you need to delete the original row. . . |
|
| Back to top |
|
|
Krishna Velamur
New User
Joined: 22 Aug 2008
Posts: 22
Location: Hyderabad
|
|
|
|
Hi,
There are two ways of doing it.
-> Insert a new record and delete the old record, as suggested by Dick.
-> Directly update using Update query (if there is no referential integrity), but in this case you need to reorg the index. This is not adviceble.
So the first solution is the best one. Corrections are welcome. |
|
| Back to top |
|
|
feellikeneo
New User
Joined: 19 Mar 2007
Posts: 73
Location: Chennai
|
|
|
|
Hi,
I have a similar query where in i need to insert a row into TABLE1 whose one of the column value should be from TABLE2. I used the below query,
| Code: |
INSERT INTO QUAL.TABLE1
VALUES
( '05'
,'XXXXX'
,(SELECT TBL2.COL1
FROM QUAL.TABLE2 TBL2
WHERE TBL2.COL2 = 'YY')
,CURRENT TIMESTAMP
)
|
But the above query failed giving a SQL code of -4700 saying
ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
Could you please help me to over come the above error.
Please correct my query if anything wrong.
Appreciate your help on this. |
|
| Back to top |
|
|
Venu Rao
New User
Joined: 01 Nov 2007
Posts: 4
Location: Westborough MA
|
|
|
|
here you go.
INSERT INTO TABLE1
SELECT
'YYYY' <---- PRIMARY KEY
,'ZZZZ' <---- PRIMARY KEY
,COL3
,COL4
,COL5
FROM TABLE1
WHERE
PMK COL1 = AAAA
AND PMK COL2 = BBBB |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello Venu Rao,
I believe there may be some correction needed for the posted sql.
If you post the correction, i'll delete this reply and the original  |
|
| Back to top |
|
|
feellikeneo
New User
Joined: 19 Mar 2007
Posts: 73
Location: Chennai
|
|
|
|
Dick,
We are using DB2 V7.1 here.
Venu,
Thank you very much for your query. I was able to make it. Here is what I used,
| Code: |
INSERT INTO QUAL.TABLE1
VALUES
( SELECT
'05'
,'XXXXX'
,TBL2.COL1
,CURRENT TIMESTAMP
FROM QUAL.TABLE2 TBL2
WHERE TBL2.COL2 = 'YY')
) |
Great piece of code !! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Neo,
Good to hear it is working - thank you for letting us know
d |
|
| Back to top |
|
|
|
|
