Thread: Check to see if customer exist in second table. Help needs withpsql
psql I have two tables , table A and Table b. Table A has list of about 60000 customers ID with all their details and Table B had customer is and age for over 500000 rows of customers. I need a query that will take a customer from table a and if that customer exists in table B then insert a yes in a new column in table a. Basically what I want to end up with in table a , is all the customers in this table and if they exist in table B then yes in a column. I.e the final result will be 60000 customers and additional column at the end with yes or no depending if they exist in table B. Any help is appreciate it -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 06/17/2018 12:19 PM, Rv02 wrote: > > > psql > I have two tables , table A and Table b. Table A has list of about 60000 > customers ID with all their details and Table B had customer is and age for > over 500000 rows of customers. I going to assume you meant Table B has customer ID and age above. Does Table B have duplicate records for customer ID? If so what I offer below might need to be revised. > > I need a query that will take a customer from table a and if that customer > exists in table B then insert a yes in a new column in table a. Basically > what I want to end up with in table a , is all the customers in this table > and if they exist in table B then yes in a column. I.e the final result will > be 60000 customers and additional column at the end with yes or no depending > if they exist in table B. For future reference there are examples below that cover this: https://www.postgresql.org/docs/10/static/sql-update.html " UPDATE employees SET sales_count = sales_count + 1 FROM accounts WHERE accounts.name = 'Acme Corporation' AND employees.id = accounts.sales_person; " I would test on a dev database or at least use: BEGIN; ... COMMIT; Untested: BEGIN; UPDATE a set new_column = 'f'; UPDATE a SET new_column = 't' FROM b WHERE a.id = b.id; ROLLBACK or COMMIT depending on outcome of above. > > Any help is appreciate it > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com