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 |
|
 |
|
|