| View previous topic :: View next topic |
| Author |
Message |
suresh_d
Beginner
Joined: 20 Sep 2006
Posts: 33
Topics: 9
|
 Posted: Wed Jul 25, 2007 4:35 am Post subject: Db2 query for manipulating a row and inserting as a new row |
 |
|
A table has following values in a column named col | Code: |
xxx1
xxx2
xxx3
yyy2
yyy4
|
For every record in col which starts with 'xxx'
a new record with 'yyy' in place of 'xxx' must be inserted.
I tried the following query but it is not working :- | Code: |
INSERT INTO tb_name(col)
SELECT 'xxx' || SUBSTR ( col, 4, 30 )
FROM table_name
WHERE col LIKE 'xxx%';
|
Also it should not give raise to a duplicate while doing so.
In the this example, we should not insert yyy2 or yyy4
as they are already present in the table. I am not sure
how to go about this.
The desired table is shown below. | Code: |
xxx1
xxx2
xxx3
yyy1
yyy2
yyy3
yyy4 |
|
|
| Back to top |
|
 |
s_shivaraj
Beginner
Joined: 21 Sep 2004
Posts: 140
Topics: 14
Location: Chennai, India
|
| Posted: Wed Jul 25, 2007 1:17 pm Post subject: |
|
|
Suresh,
Try this.. It worked for me..
| Code: | INSERT INTO tb_name(col)
SELECT 'YYYY' CONCAT SUBSTR(col, 4,30) FROM
tb_name AA WHERE
AA.col LIKE 'XXXX%' AND
NOT EXISTS
( SELECT 1 FROM tb_name A WHERE
A.col = 'YYYY' CONCAT SUBSTR(AA.col, 4,30))
; |
_________________
Cheers
Sivaraj S
'Technical Skill is the Master of complexity, while Creativity is the Master of Simplicity' |
|
| Back to top |
|
|
suresh_d
Beginner
Joined: 20 Sep 2006
Posts: 33
Topics: 9
|
| Posted: Tue Jul 31, 2007 5:55 am Post subject: |
|
|
Hi Shivraj,
The first query for replacing all xxx with yyy worked. But the query to eleminate duplicates is not working. Now I need a query for only the below problem.
I have following records -
xxx1
xxx2
xxx3
yyy1
I want a copy of all the xxx records for yyy in to the same table with out duplicates.
o/p :
xxx1
xxx2
xxx3
yyy1
yyy2
yyy3
Thansk a ton
Suresh |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12383
Topics: 75
Location: San Jose
|
| Posted: Tue Jul 31, 2007 6:31 am Post subject: |
|
|
suresh_d,
try this
| Code: |
INSERT INTO Table_name(COL) SELECT A.C1
FROM (SELECT CHAR('YYY') ||
SUBSTR(COL,4,27) AS C1
FROM Table_name
WHERE COL LIKE 'XXX%') A
WHERE A.C1 NOT IN (SELECT COL
FROM Table_name
WHERE COL LIKE 'YYY%')
;
|
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
suresh_d
Beginner
Joined: 20 Sep 2006
Posts: 33
Topics: 9
|
| Posted: Tue Jul 31, 2007 9:35 am Post subject: |
|
|
Hi Kolusu,
Sorry I posted my problem littile bit wrong, I mixed two my queries and lead me to confusion. One query is solved with Sivaraj reply. Now the other is below.
I have to replace the entire row of table by changing one column say xxx with yyy.
eg :
xxx 111 222
xxx 123 123
xxx 232 343
xxx 123 124
yyy 123 123
o/p
xxx 111 222
xxx 123 123
xxx 232 343
xxx 123 124
yyy 123 123
yyy 111 222
yyy 232 343
yyy 123 124
If I use the above query on this as the column is not unique.. Not in is resulting no rows.. Please help me on this.. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12383
Topics: 75
Location: San Jose
|
| Posted: Tue Jul 31, 2007 9:43 am Post subject: |
|
|
suresh_d,
You need to provide more details. When you say entire row how many columns are involved? How is the primary key defined? what is the format and length of the columns involved? How is the duplicate row identified is it based on the primary key or entire row of the table?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
