[FIXED] How to update record with the same data of existing record

Issue

How to update record many columns with the same data of existing record in the same table .

For Example :

id  name  m_value

 1   moh   1000

 2   jo       0

---

I want the record with id =2 updated with the same data of id =1

Data After should be like this :

id  name  m_value

 1   moh   1000

 2   moh   1000

Solution

Please give your tables names — it's impossible to write satisfactory answers when the names of the relevant tables are unknown. In the absence of better information, your table is AnonymousTable.

You need a sub-query:

UPDATE AnonymousTable
   SET (name, m_value) = ((SELECT name, m_value
                             FROM AnonymousTable 
                            WHERE id = 1
                         ))
 WHERE id = 2;

The key trick here is the double parentheses. When you use the notation SET(a, b, c) = (v1, v2, v3), you need the one set of parentheses on each side of the = sign. When you use a sub-select to generate a list of values, you enclose it in parentheses. So, when you both set a list of columns and use a sub-query, you need the double parentheses.

You almost certainly want a condition on the main UPDATE statement as well as the condition in the sub-query as shown. If there is no row with id = 1, then you will assign nulls, unless you have NOT NULL constraints on name or m_value — if they're there, as they should be, then the update will fail, which is good.

Note that there isn't an 'all columns except …' shorthand so if there are 30 columns in the table, you'll have to list 29 of them (all except id) in the LHS of the SET and again in the select-list of the sub-select.

---

Demo

DROP TABLE IF EXISTS AnonymousTable;

CREATE TEMP TABLE AnonymousTable
(   
    id INTEGER NOT NULL,
    NAME CHAR(10) NOT NULL,
    m_value CHAR(10) NOT NULL
);  
INSERT INTO AnonymousTable VALUES(1, 'moh', '1000');
INSERT INTO AnonymousTable VALUES(2, 'jo', '0');

SELECT * FROM AnonymousTable;

UPDATE AnonymousTable
   SET (NAME, m_value) = ((SELECT NAME, m_value
                             FROM AnonymousTable 
                            WHERE id = 1 
                         ))  
 WHERE id = 2;

SELECT * FROM AnonymousTable;                                    

Output:

1|moh|1000
2|jo|0

1|moh|1000
2|moh|1000

Tested: Informix 12.10.FC6 on Mac OS X 10.11.6 (SQLCMD 90.01, ESQL/C 4.10.FC6).

Some really old versions of Informix may object to the sub-query against the same table as being updated. You shouldn't really be using such an old system, but if it fails for you, that may be why.

Answered By - Jonathan Leffler

Answer Checked By - Willingham (PHPFixing Volunteer)