Merging of rows in a table

[niksni]

hi

i want to merge two rows in a table. but a little confused.. Please help!:confused:

the example is as shown below

Record #1: Nikhil Bhat 1Jan2009 Married ABC

Record #1: Nikhil Ong 1Feb2009 Single DEF

Meged
Record : Nikhil Ong 1Jan2009 Married DEF

There is a primary key 'ID' which is also auto incremented.

[darkagn]

Ok, do you want to update the existing record (and if so, what is the ID of that record) or insert a new record with the combined data of the other two (what are their ID's)?

[niksni]

yes it is updating the first row and deleting the second.

the first row has a ID ='10'
the second row has a ID = '214'

so in a sense i want to update the row with ID '10' with '214' with the coloumns which i have marked in bold.

[mwasif]

Use the following query to update the record

UPDATE t1, (SELECT * FROM t1 where id=214) as t2  SET
t1.last_name = t2.last_name,
t1.date_column = t2.date_column,
t1.marital_status = t2.marital_status
WHERE t1.id = 10

After this query, simply run a 2nd query to delete the 2nd record.

DELETE FROM t1 WHERE id=214