Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Gil149
Starting Member
5 Posts |
Posted - 2014-09-20 : 12:45:58
|
I have 3 queries and I need to see if there is a way to combine them since they do the same thing, or if there is a more efficient way I am missing. I run the query below on Table A to find the product first for TableA.Model=1, then for Model=2 and then Model=3. The reason I split it into three queries is I need Model=1 Customers only, then Model=2 only if there isn't a Model=1 Customer, etc. Any help is appreciated.UPDATE Table1 INNER JOIN TableA ON Table1.Product = TableA.Product SET Table1.Customer = [TableA].[Customer]WHERE (((TableA.Model)="1") AND ((Table1.Customer) Is Null)); |
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-20 : 12:57:15
|
how about:update t1set t1.customer = tA.customrefrom table1 t1join tableA tA on t1.Product = tA.productwhere tA.model = 1 OR tA.model = 2 AND NOT EXITS ( select 1 from tableA tA where t1.product = tA.product and tA.model <> 1) OR tA.model = 3 AND NOT EXITS ( select 1 from tableA tA where t1.product = tA.product and tA.model not in (1,2)) |
|
|
Gil149
Starting Member
5 Posts |
Posted - 2014-09-20 : 14:56:37
|
gbritton,I can get as far as I have in the SQL below. When I get into the other parts of the WHERE statement with the first OR, I get exceedingly confused by how the code appears, specifically at: AND NOT EXITS ( select 1 from tableA tA where t1.product = tA.product and tA.model <> 1)If you have time, I know everyone is busy, can you assist in the syntax with this. I am using Access as the front end to an Oracle database, if that helps. I apologize for my limited understanding of SQL. It's something I am trying to rectify.This is what I have so far that works with Access:UPDATE Table1 INNER JOIN TableA ON [Table1].[Product]=[TableA].[PRODUCT] SET Table1.Customer = [TableA].[CUSTOMER]WHERE ((([TableA].[MODEL])="Retail") And (([Table1].[Customer]) Is Null)); |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-20 : 15:43:21
|
The idea is to only consider model = 2 if there is no model =1. That's what the not exists subquery does. Note that the syntax is for SQL server since that's what I know. |
|
|
|
|
|