0

I want to update table 1 referring from values of table 3 and table 2

Table1

enter image description here

Table2

enter image description here

Table3

enter image description here

I want to update column 'price' of table1 where the value of store_div of table1's 'store_num' from table3 is equals to store_div from table2.

I already tried joining table1 and table3 to get each store_num's store_div value but I don't know where to point my where clause because store_div is not present on table1.

The update would look like this:

Update Table1 t1 set price = '0'
join Table t3 on t1.store_num = t3.store_num
where ([t1.store_num]'s [store_div] = Table2.store_div)

I'm not really sure on the where clause tho.

Any help would be appreciated.

John F
  • 142
  • 2
  • 13
  • 3
    Possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) – Chris Pickford Jul 07 '16 at 14:45
  • @John F, are you trying to update the price to '0' for every store, where that store is in a particular division? I just want to be a bit clearer on what you are trying to do. – Aidan Jul 07 '16 at 14:52
  • yes, where store's division number is present on table2 – John F Jul 07 '16 at 15:03
  • Hi @ChrisPickford I don't think that it is a duplicate, because I'm trying to update a table where the value from where clause is not in the table that will be updated. – John F Jul 07 '16 at 15:11

1 Answers1

0

This should work in SQL Server. 

Update Table1 
set price = '0'
From Table3 t3 
inner join Table1 t1
  on t1.store_num = t3.store_num
inner join Table2 t2
  on t2.Store_Div = t3.Store_Div

Your join was close, but you need to include Table2 to ensure only Stores where Division is logged in Table2 are updated.

Aidan
  • 171
  • 3
  • 12