SQL Join Update table

Question

I have two tables, Table A has user Id and 5 different product columns(empty, to be filled by count ). Table B has timestamp user id and product id ( purchased at time t). This code id giving error

update table_A as table_A 
  set Count_Product_1 = (select count(product_ID)
                         from Table_B inner join Table_A 
                           on Table_A.User_ID=Table_B.User_ID 
                         where Product_ID = 'Unique_identifier_product_1');

error: You cannot reopen Table_A for update access with member-level control because Table_A is in use by you in resource environment SQL

score 3 · Accepted Answer · answered Oct 19 '15 at 11:26

3

I am guessing that you want a correlated subquery, not a general subquery. Perhaps this does what you want:

update table_A as a 
    set Count_Product_1 = (select count(b.product_ID)
                           from Table_B b
                           where a.User_ID = b.User_ID and
                                 b.Product_ID = 'Unique_identifier_product_1'
                          );

This just seems like a more reasonable query.

answered Oct 19 '15 at 11:26

Gordon Linoff

1,242,037
58
646
786

thanks Its working , however my table has 400,000+ records so its taking time in (SAS Env) , results awaited. – Zyerr Dwij Oct 19 '15 at 12:06
An index on `table_B(user_id, product_id)` would help speed the query. – Gordon Linoff Oct 20 '15 at 00:39

SQL Join Update table

1 Answers1