CODEWITHSUNDEEP
mysql
lisovaccaro
lisovaccaro

Reputation: 33956

Duplicate row changing certain columns?

I want to copy row with ID = 1 to a new row in the same table. But two columns (UID and Date) must not be the same as the original row.

INSERT IGNORE INTO `Posts` (`A`,`B`,`UID`,`Date`) VALUES

How do I get all values from row with ID = 1 but change UID to 2 and Date to NOW()?

Upvotes: 1

Views: 61

Answers (3)

Nir Alfasi
Nir Alfasi

Reputation: 53535

Try:

INSERT IGNORE INTO `Posts` (`A`,`B`,`UID`,`Date`) 
select a, b, 2, now() from `Posts` where UID = 2;

See example

Upvotes: 0

Anant Dabhi
Anant Dabhi

Reputation: 11104

INSERT INTO `Posts` (`A`,`B`,`UID`,`Date`) 

SELECT col1, col2, `your definedvalue1`,`your defined date `,
 FROM this_table
WHERE 1>0(your condition);

Upvotes: 1

xkeshav
xkeshav

Reputation: 54032

TRY

INSERT INTO Posts (`A`,`B`,`UID`,`Date`) 
SELECT `A`,`B`,`UID`,`Date` FROM Post
ON DUPLICATE KEY UPDATE UID =2 DATE =NOW()

Note: make index on UID and Date column

Upvotes: 0

Related Questions