|
View previous topic :: View next topic
|
| Author |
Message |
danylele74
New User
Joined: 03 Jul 2014
Posts: 28
Location: Italy
|
|
|
|
Hi all,
i have this problem:
| Code: |
Table A.
COl 1
-----------
yyKKK cc
KKK aa ssss
KKKqqqjj
KKKffff1122
yy KKKWWW
1234KKKcc
a123f tttKKK |
I must insert the same records but chancing the KKK value in an another value (i.e ZZZ)
Table A (result).
| Code: |
COl 1
-----------
yyKKK cc
KKK aa ssss
KKKqqqjj
KKKffff1122
yy KKKWWW
1234KKKcc
a123f tttKKK
yyZZZ cc
ZZZ aa ssss
ZZZqqqjj
ZZZffff1122
yy ZZZWWW
1234ZZZcc
a123f tttZZZ |
Can I do this with a query? If yes, how?
thank you. |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Code: |
update table a
set a.col1 =
( select replace(b.col1,'KKK','ZZZ') from table b
where a.key = b.key
) |
|
|
| Back to top |
|
|
danylele74
New User
Joined: 03 Jul 2014
Posts: 28
Location: Italy
|
|
|
|
I must INSERT the new records not update the key field.
Take a look of the result (last screenshot)
bye |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
look here
| Code: |
INSERT INTO table a
SELECT replace(b.col1,'KKK','ZZZ')
where FROM table b
LOCATE('KKK', col1) <> 0 |
| Code: |
| LOCATE('KKK', col1) <> 0 |
is just to avoid key violation as you say above. |
|
| Back to top |
|
|
danylele74
New User
Joined: 03 Jul 2014
Posts: 28
Location: Italy
|
|
|
|
This is my query
| Code: |
SELECT REPLACE(col1,'KKK','ZZZ'))
from db2.TABLE
where bank = 'aa' |
Total records: 600
| Code: |
SELECT COUNT(*)
from db2.TABLE
where bank = 'aa'
and col1 like '%KKK%' |
Total records: 200
Why this difference?
Thanks |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| Because for the first query you are pulling all records with bank = 'aa' and the second query you restrict the pull to only those records with bank='aa' and that have coll containing 'KKK' somewhere? |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
Just to add to what Robert said:
In the first query, You are fetching 600 rows but doing a replace for only 200 of them. Count (first query Total Records) is showing how many rows are fetched and it only depends on the condition bank='aa'.
. |
|
| Back to top |
|
|
|
|
