2

How to copy a set of rows based on content of one column into the same table and change the content of the conditional column in duplicated rows to something else.

select x, y, z from table A where z="B" ... 

copy into table A with incremented IDs ...

and in the new duplicated rows change z="C"

EDIT: I could do following (will it work?) - the Primary Index key is Auto-Incremented

INSERT INTO table A (x, y, z) SELECT x, y, z FROM table A WHERE z="B";

but how do I change z="B" in new duplicated rows at the same time to z="C" ?

Please edit / improve my attempt. Thanks.

TedH
  • 21
  • 2
  • 3
    Possible duplicate of [Update row with data from another row in the same table](https://stackoverflow.com/questions/5574434/update-row-with-data-from-another-row-in-the-same-table) – Obsidian Age Aug 14 '18 at 04:44
  • how exactly should I format the statement, please ? for my specific scenario. thanks. – TedH Aug 14 '18 at 05:03

1 Answers1

0

You can use union:

SELECT x,y
CASE z
    WHEN 'B' then 'C' else  z
END
FROM A
ORDER By id

union

SELECT x,yz
FROM A
ORDER By id
franiis
  • 1,378
  • 1
  • 18
  • 33
Lê Phượng
  • 1