CODEWITHSUNDEEP
sqlsql-servert-sql
Dan Orlovsky
Dan Orlovsky

Reputation: 1095

SQL Update Column based on separate table conditions

I've the task of updating several entries in a database.

Two tables need to be connected by an Id. One is an account table and the other is a territory table.

I have a TerritoryID in the Account table, and that entry will have to be updated with the Id of a territory based off of it's DisplayName.

ACCOUNT TABLE

AccountId
TerritoryId << I need to populate this.

TERRITORY TABLE
Id
DisplayName

I have a spreadsheet with AccountId's and DisplayNames. I need a query that will update one table based on one condition (accountId) with a value based on another condition from another table (based on DisplayName).

I've been trying things similar to:

UPDATE 
 [dbo].[Account] 
SET 
 TerritoryId = [dbo].[Territories].Id 
FROM Accounts ON WHERE AccountId = '6477026' SELECT Id FROM Territories WHERE DisplayName LIKE '%partialDisplayName'

I was also trying to integrate a CASE statement in there. I just can't seem to make anything stick, though. The potential duplicate answers I've found don't see to take two conditions from two separate tables into consideration.

Upvotes: 0

Views: 56

Answers (1)

Adam
Adam

Reputation: 4168

The following is a fabrication of your problem. Your tables...

create table #account (
  accountId int not null primary key
, territoryID int null
)

create table #territory (
  territoryId int not null primary key
, displayName varchar(20)
)

Some sample data...

insert into #territory values (1, 'Hell');
insert into #territory values (2, 'heaven');
insert into #territory values (3, 'purgatory');

insert into #account values (1, 0)
insert into #account values (2, 0)
insert into #account values (3, 0)
insert into #account values (4, 0)
insert into #account values (5, 0)
insert into #account values (6, 0)
insert into #account values (7, 0)
insert into #account values (8, 0)

I have a spreadsheet with AccountId's and DisplayNames. I need a query that will update one table based on one condition (accountId) with a value based on another condition from another table (based on DisplayName).

Option-1: In excel, craft the update statements, copy those statements from Excel to your query editor, and run them. The query looks like the following:

UPDATE #account
SET territoryID = (SELECT territoryId FROM #territory WHERE displayName = '<name>')
WHERE accountID = <id>

Option-2: You import the spreadsheet's contents into Excel (lots of ways to do this, Google is your friend). 

--Create table to store the temp data
CREATE TABLE #excel_stuff (accountId int, displayName varchar(20));

--Created insert statements for the data from the spreadsheet. Running
--the inserts.
insert into #excel_stuff values (1, 'heaven')
insert into #excel_stuff values (2, 'heaven')
insert into #excel_stuff values (3, 'hell')
insert into #excel_stuff values (4, 'heaven')
insert into #excel_stuff values (5, 'heaven')
insert into #excel_stuff values (6, 'purgatory')
insert into #excel_stuff values (7, 'purgatory')
insert into #excel_stuff values (8, 'hell')

At this point your Excel data is in the database. Now, I'll update the territoryId values in the #account table:

UPDATE #account
SET territoryID = (
SELECT t.territoryID
FROM #excel_stuff ex INNER JOIN #territory t
ON ex.displayName = t.displayName
WHERE ex.accountId = #account.accountId
)

DROP TABLE #excel_stuff;

Good luck!

Upvotes: 2

Related Questions