How to change a table row value to another table row value in postgresql-CodePudding

I have database in postgresql which have a schema inside which i have many tables. i have a specific table that i want. this table contain many fields, in which there is a field named user1_id. this field contains numbers. there are 4 users. The user1_id field goes like given below:

user1_id
--------
   1
   1
   2
   4
   3
   4
   .
   .
 Goes-on

this user1_id is a primary key in another table in the same schema. and the name of this field is named "id" in the other table. This id field represents a field named "username" which is the name of the users. The username field is given below:

id  username
--  --------
1     alan
2     ali
3     joe
4     aadhi

as i mentioned earlier there are 4 users and these are the names. now i want to replace the user1_id to the username rows according to the 'id'. ie, if 1 = alan, i want 'alan' to appear in the first table whenever 1 shows up in "user1_id". how can i do it in postgresql? or as a sql statement in python?

CodePudding user response：

A basic join should suffice here:

SELECT t1.user1_id, t2.username
FROM table1 t1
LEFT JOIN table2 t2
    ON t2.id = t1.user1_id;

CodePudding user response：

To update a table row value to another table row value in PostgreSQL, you can use the following statement:

UPDATE table_name
SET column_name = (SELECT column_name FROM table_name WHERE condition)
WHERE condition;

In this statement, table_name is the name of the table, column_name is the name of the column whose value you want to update, and condition is a condition that specifies which rows to update.

For example, to update the price column in the products table to the value in the price column of the new_prices table for rows where the id column in the products table matches the id column in the new_prices table, you can use the following statement:

UPDATE products
SET price = (SELECT price FROM new_prices WHERE id = products.id)
WHERE id IN (SELECT id FROM new_prices);

This statement will update the price column in the products table for all rows where the id column matches a value in the new_prices table.

It's important to note that the subquery in the SET clause must return only one row, otherwise the statement will fail. You can use the LIMIT clause in the subquery to ensure that it only returns one row.

For example, the following statement will update the price column in the products table to the first matching value in the new_prices table:

UPDATE products
SET price = (SELECT price FROM new_prices WHERE id = products.id LIMIT 1)
WHERE id IN (SELECT id FROM new_prices);

I hope this helps!