how can i do update select in MySQL?

Question

I have one table content, where in I am adding information regarding pages.

id history_id title body.
1   0          home   this home
2   1          home   here is sometext.

I am using history_id to keep track of changes made to the page, here I want to restore the changes made to the record id = 1 and simultaneously create new record with previous content.

I have tried this.

INSERT INTO content(id, history_id, title, body)
select id, history_id, title, body;
UPDATE content (SELECT title, body WHERE id = 1)
SET title = 'title', body='body';

it is successfully creating new record with previous content but not updating the current record. can anyone help me out how can I do update select ? and what changes should i have to made in here ? Thanks.

sjagr · Answer 1 · 2013-12-25T22:42:37.793

0

Your UPDATE query isn't written properly

UPDATE content SET title = 'title', body = 'body' WHERE id = 1;

To use a SELECT with your UPDATE:

UPDATE content a LEFT JOIN content b ON (a.id = b.history_id AND a.id = 1)
    SET a.title = b.title, a.body = b.body;

I'm not completely sure on how your content history is supposed to work but I formed a query based on my best guess

edited Dec 25 '13 at 22:42

answered Dec 25 '13 at 22:21

sjagr

15,983
5
40
67

@waseem There is no natural `UPDATE ... SELECT` syntax. [Does this help you?](http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query) – sjagr Dec 25 '13 at 22:32
@waseem I've modified my answer to include a SQL query for your problem – sjagr Dec 25 '13 at 22:44

score 0 · Answer 2 · answered Dec 25 '13 at 23:05

0

I'm not entirely sure I understand what you want, but to update based on an existing record do

UPDATE content a, content b SET a.title = b.title, a.body = b.body WHERE a.id = '1' AND b.id = '2';

Where a.id is the record you want to replace and b.id is the record you want to copy from. You might also want to include a WHERE clause on your insert statement otherwise you'll duplicate every record in the table:

INSERT INTO content(id, history_id, title, body) SELECT id, history_id, title, body FROM content WHERE id = 1;

Where id is the record you want to copy

based on mysql forums

answered Dec 25 '13 at 23:05

leeman

462
3
9

I am updating the record and creating a new record where in I am saving previous record to track history. If any user wants to get back those changes or wants to restore those changes. So how can we restore the current with previous values? – Suleman khan Dec 26 '13 at 10:18
You want to set the body and title of id=1 to the body and title of id=2? – leeman Dec 26 '13 at 12:50

score 0 · Accepted Answer · answered Dec 26 '13 at 16:12

0

finally i figured out to do update select.

UPDATE content c, 
(SELECT 
    c1.title AS title, 
    c1.body AS body
 FROM content c1
 WHERE c1.id = x) AS c2
 SET
    c.title = c2.title,
    c.body = c2.body
WHERE id = x;

answered Dec 26 '13 at 16:12

Suleman khan

1,038
4
14
34

how can i do update select in MySQL?

3 Answers3