CODEWITHSUNDEEP
postgresql
qwe001
qwe001

Reputation: 633

How can I change old record by new record on postgresql?

Here is my records.

SELECT * FROM items;

 id | show | status | user_id | name | note   | note_mod | test  | test_mod
----+------+--------+---------+------+--------+----------+-------+----------
  1 | t    |      1 |         | Adam | aaa1   |          | test1 | 
  2 | f    |      2 |       1 | Adam | aaa1   | aaa2     | test1 | test_new1
  3 | t    |      1 |         | Tom  | blabla |          |       | 
  4 | f    |      2 |       3 | Tom  | blabla | baaa     |       | ayyyyyy
  5 | t    |      1 |         | Eva  |        |          |       | 
  6 | f    |      2 |       5 | Eva  |        | meow     |       |

I wanna change note,test 1 and 3 values from note_mod,test_mod 2 and 4 like this.

 id | show | status | user_id | name | note   | note_mod | test  | test_mod
----+------+--------+---------+------+--------+----------+-------+----------
  1 | t    |      1 |         | Adam | aaa1   | aaa2     | test1 | test_new1
  2 | f    |      2 |       1 | Adam | aaa1   | aaa2     | test1 | test_new1
  3 | t    |      1 |         | Tom  | blabla | baaa     |       | ayyyyyy
  4 | f    |      2 |       3 | Tom  | blabla | baaa     |       | ayyyyyy
  5 | t    |      1 |         | Eva  |        | meow     |       | 
  6 | f    |      2 |       5 | Eva  |        | meow     |       |

id 2 user_id and id 1 is same, id 4 user_id and id 3 is same.

I know select new record only like this code.

SELECT new.* FROM items old, items new WHERE old.id = new.user_id AND old.id != new.id;

 id | show | status | user_id | name | note   | note_mod | test  | test_mod
----+------+--------+---------+------+--------+----------+-------+----------
  2 | f    |      2 |       1 | Adam | aaa1   | aaa2     | test1 | test_new1
  4 | f    |      2 |       3 | Tom  | blabla | baaa     |       | ayyyyyy
  6 | f    |      2 |       5 | Eva  |        | meow     |       |

But I have no idea to write UPDATE method. Anyone knows?

P.S.

Thanks for reply GROX13

What I need to do is change value from another records.

if i run this code

UPDATE items
SET (note_mod, test_mod) = ('new1', 'test_new1') WHERE id = 1;

record will change like this you know.

 id | show | status | user_id | name | note   | note_mod | test  | test_mod
----+------+--------+---------+------+--------+----------+-------+----------
  1 | t    |      1 |         | Adam | aaa1   | new1     | test1 | test_new1

But that code need to change every string 'new1', 'new2', 'new3' bla bla bla...

I am looking for the code like this.

UPDATE items
SET old(note_mod, test_mod) = new(note_mod, test_mod) WHERE id = 1;

old is mean id 1's note_mod. new is mean id 2's note_mod. but I dont know how to define old and new on UPDATE. Any ideas?

I wanna match them from user_id. status = 1 mean user register account (example.com/create) then user_id, note_mod, test_mod always null. name is required. note, test is nullable.

status = 2 mean edit profile (example.com/1/edit) then user_id set id automatically ($data -> user_id = $request -> id)

if user change profile, profile not change immediately. profile can be change by my sql code only.

if user change note or test, that set into note_mod or test_mod, not into note or test. ($data -> note_mod = $request -> note) note, test, note_mod, test_mod is nullable.

Upvotes: 0

Views: 92

Answers (2)

joop
joop

Reputation: 4503

You were almost there when you wrote:

SELECT new.* FROM items old, items new WHERE old.id = new.user_id AND old.id != new.id;

(replacing {old,new} by {o,n}, because they are keywords)

UPDATE items o
SET note_mod = n note_mod
  , test_mod = n.test_mod
FROM items n
WHERE o.id = n.user_id
AND o.id <> n.id
-- avoid null updates:
-- AND (o.note_mod <> n.note_mod OR o.test_mod <> n.test_mod)
  ;

Upvotes: 0

GROX13
GROX13

Reputation: 4765

You can do so if you only have four entries: 

UPDATE items
SET (note_mod, test_mod) = ('new1', 'test_new1') WHERE id = 1;

UPDATE items
SET (note_mod, test_mod) = ('new2', 'test_new2') WHERE id = 3;

For your case I think this should work but have not tested:

UPDATE items SET (note_mod) = (note) WHERE note_mod IS NULL AND note IS NOT NULL;

UPDATE items SET (test_mod) = (test) WHERE test_mod IS NULL AND test IS NOT NULL;

I'll check and update answer.

Upvotes: 1

Related Questions