|
View previous topic :: View next topic
|
| Author |
Message |
krsenthil85
New User
Joined: 31 Aug 2007
Posts: 55
Location: bangalore
|
|
|
|
Hi All,
I have a requirement like, I want to duplicate some rows within a table XX, except single field. For example if we have Field name as AA BB CC DD, I want to insert the values of AA BB CC as same in the existing row for certain where condition but i have to pass the value of DD externally in query.
Can we achieve this through a single query.
Please give me the solution. |
|
| Back to top |
|
 |
ascelepius
New User
Joined: 16 Jun 2008
Posts: 35
Location: bangalore
|
|
|
|
You could try this,
Consider you have Table TABLE1 with columns COL1,COL2,COL3 & COL4 with data,
| Code: |
COL1 COL2 COL3 COL4
===============
AA BB CC DD
|
now if you want to duplicate this row for all field except COL4,
you can give,
| Code: |
insert into table1(COL1, COL2, COL3, COL4)
select COL1, COL2, COL3, <value> from table1
where COL1 ='AA' and COL2='BB' and COL3='CC' and COL4= 'DD'
|
here <value> is the data you need insert.
regards,
asci(i) |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Ascelipius ,the above query is nothing but
| Code: |
insert into table1(COL1, COL2, COL3, COL4)
values ('AA','BB','CC',<value>)
|
as the values of col1 , col2 and col3 are goin to be the same as in where clause ...
senthil,
can you provide an example for wht you are expecting with data... |
|
| Back to top |
|
|
ascelepius
New User
Joined: 16 Jun 2008
Posts: 35
Location: bangalore
|
|
|
|
@ashimer, Isn't that what he wants?
| Quote: |
I want to duplicate some rows within a table XX, except single field. For example if we have Field name as AA BB CC DD, I want to insert the values of AA BB CC as same in the existing row for certain where condition but i have to pass the value of DD externally in query.
|
|
|
| Back to top |
|
|
ascelepius
New User
Joined: 16 Jun 2008
Posts: 35
Location: bangalore
|
|
|
|
But yes he could achieve it a lot simpler if either col1, col2 or col2 were part of a unique index...
regards,
asci(i) |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
... the question is a bit confusing, but still the TS said duplicate
the select with the where clause based on equality for col1 col2 col3
is needed to check for the existence ( not for the insert really ) |
|
| Back to top |
|
|
krsenthil85
New User
Joined: 31 Aug 2007
Posts: 55
Location: bangalore
|
|
|
|
Hi ascelepius,
The query that u wrote is correct, But i am getting error message as
QUERY MESSAGES:
Use only one column after SELECT in the subquery.
My exact query looks like this
INSERT INTO GLDB01.GLCAIT_COLCT_ACCT VALUES
(SELECT
COL1, COL2, COL3 , 'input value',
COL5, COL6 FROM GLDB01.GLCAIT_COLCT_ACCT
WHERE COL3= 'LMS')
Here I am Passing 'input value' in the place of COL4 |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
change your query as depicted earlier by ASCI..
| Code: |
INSERT INTO GLDB01.GLCAIT_COLCT_ACCT (COL1, COL2, COL3 , COL4) VALUES
(SELECT
COL1, COL2, COL3 , 'input value',
COL5, COL6 FROM GLDB01.GLCAIT_COLCT_ACCT
WHERE COL3= 'LMS')
|
|
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Missed COL5 and COL6 ... |
|
| Back to top |
|
|
krsenthil85
New User
Joined: 31 Aug 2007
Posts: 55
Location: bangalore
|
|
|
|
Hi All,
Thanks for your kind support, the query is working fine.
-Senthil. |
|
| Back to top |
|
|
|
|
