get a value for each data and update older records after adding new column

userarO51 0
Viewed 20

I have added a new column 'bookCode' to an existing table.

For now, when new books are added to a table, before inserting we do some requests for each book, get their unique bookcode and insert it to the table.

The problem here is how to update older book records' bookcodes. So for each book that exist in table we need to get the bookcode and update the field. What you can advice? What are the best practices?

I am using nodejs and postgresql.

scheduled-tasks cron postgresql
1 Answers

You can write an update request for records where bookCode is null. To write this query, you must use the same logic that you use before inserting records. I don't know your table structure. If your book codes stores in same table, you can update these using join to the same table. If not, then join to another table. I wrote some sample queries for updating.

Sample 1. (Update book codes from same table):

CREATE TABLE books (
    id int4 NOT NULL DEFAULT nextval('newtable_id_seq'::regclass),
    bookcode int4 NULL,
    bookname varchar NULL,
    CONSTRAINT newtable_pk PRIMARY KEY (id)
);

INSERT INTO books (id, bookcode, bookname) VALUES(1, NULL, 'Book1');
INSERT INTO books (id, bookcode, bookname) VALUES(2, NULL, 'Book2');
INSERT INTO books (id, bookcode, bookname) VALUES(3, 1245, 'Book1');
INSERT INTO books (id, bookcode, bookname) VALUES(4, 1655, 'Book5');
INSERT INTO books (id, bookcode, bookname) VALUES(5, 2211, 'Book4');
INSERT INTO books (id, bookcode, bookname) VALUES(6, 1219, 'Book8');
INSERT INTO books (id, bookcode, bookname) VALUES(7, 9955, 'Book2');
INSERT INTO books (id, bookcode, bookname) VALUES(8, NULL, 'Book10');
INSERT INTO books (id, bookcode, bookname) VALUES(9, 3357, 'Book10');


update books b1 
set 
    bookcode = b2.bookcode
from books b2 
where 
         b1.bookcode is null 
     and b1.bookname  = b2.bookname 
     and b2.bookcode is not null

Sample 2. Update book codes from another table

update books b1 
set 
    bookcode = b2.bookcode
from book_code_table b2 
where 
         b1.bookcode is null 
     and b1.bookname  = b2.bookname
Ramin Faracov0
Related