How to update record with the same data of existing record

Question

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

Use UPDATE SELECT. You may refer to this answer: [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) — Albert Israel, Jul 26 '16 at 10:11
@jarlh : i have tried to clarify what i mean.Could you help plz — Anyname Donotcare, Jul 26 '16 at 10:27
Please, please, please include a table name in the question! It's a bit difficult to address Old What's His Name if you don't what his name is; it's difficult to write good answers to your queries if you don't include table names. — Jonathan Leffler, Jul 26 '16 at 17:10

score 1 · Accepted Answer · answered Jul 26 '16 at 17:06

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.

WoooW,You are a legend ,Genus , that's exactly what i want , i hope there was an `except method to select all except columns` , but it works very well.I'm so sorry for making you nervous by not mention the table name.I thought it's just a sample data and useless to mention the table name in that case .Thanks a lot — Anyname Donotcare, Jul 27 '16 at 09:19
We don't need to know the exact table name, but specifying some table name makes it easier to get consistent help, especially if there is more than one table involved. If the answers all have to make up table names, things get horribly difficult. Or, at least, more difficult than necessary. — Jonathan Leffler, Jul 27 '16 at 14:31

score 0 · Answer 2 · answered Jul 26 '16 at 10:51

0

UPDATE target_table T
SET T.m_value = (SELECT T1.m_value FROM target_table T1 WHERE T1.id = '1')

This code will work only if the "id" column is set to a Primary Key, otherwise it could return more values, ending with a query exception.

answered Jul 26 '16 at 10:51

Andrea Montanari

88
1
9

score 0 · Answer 3 · answered Apr 25 '22 at 03:08

Try This ...

$this->db->select()
->from('AnonymousTable')
->where('id', 1);
$NewData = $this->db->get()->row();
$data = ([
  'name' => $NewData->name,
  'm_value' => $NewData->m_value
]);
$this->db->where('id', 2);
$this->db->update('AnonymousTable', $data);

How to update record with the same data of existing record

3 Answers3

Demo