'How to update record with the same data of existing record
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 1:[1]
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.
Solution 2:[2]
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.
Solution 3:[3]
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);
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Jonathan Leffler |
Solution 2 | Andrea Montanari |
Solution 3 | P. Meta |