Run 3 updates in one? - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Run 3 updates in one?

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 t1
set t1.customer = tA.customre
from table1 t1
join tableA tA
  on t1.Product = tA.product
where 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources