CODEWITHSUNDEEP
sql-servert-sqlsql-updateleft-joininsert-update
heyNow
heyNow

Reputation: 886

SQL SERVER update or insert after left join

I have a Table Animals 

Id | Name     | Count | -- (other columns not relevant)
1  | horse    | 11   
2  | giraffe  | 20

I want to try to insert or update values from a CSV string

Is it possible to do something like the following in 1 query?

;with results as
(
 select * from
 (
  values ('horse'), ('giraffe'), ('lion')
 )
  animal_csv(aName)
  left join animals on
  animals.[Name] = animal_csv.aName
)
update results 
set
[Count] = 1 + animals.[Count]
-- various other columns are set here
where Id is not null

--else
--insert into results ([Name], [Count]) values (results.aName, 1) 
-- (essentially Where id is null)

Upvotes: 0

Views: 164

Answers (1)

Alan Samet
Alan Samet

Reputation: 1138

It looks like what you're looking for is a table variable or temporary table rather than a common table expression.

If I understand your problem correctly, you are building a result set based on data you're getting from a CSV, merging it by incrementing values, and then returning that result set.

As I read your code, it looks as if your results would look like this:

aName    | Id | Name    | Count
horse    | 1  | horse   | 12
giraffe  | 2  | giraffe | 21
lion     |    |         |

I think what you're looking for in your final result set is this:

Name     | Count
horse    | 12
giraffe  | 21
lion     | 1

First, you can get from your csv and table to a resultset in a single CTE statement:

;WITH animal_csv AS (SELECT * FROM (VALUES('horse'),('giraffe'), ('lion')) a(aName))
SELECT ISNULL(Name, aName) Name
,   CASE WHEN [Count] IS NULL THEN 1 ELSE 1 + [Count] END [Count] 
    FROM animal_csv
        LEFT JOIN animals
            ON Name = animal_csv.aName

Or, if you want to build your resultset using a table variable:

DECLARE @Results TABLE
(
  Name  VARCHAR(30)
, Count INT
)

;WITH animal_csv AS (SELECT * FROM (VALUES('horse'),('giraffe'), ('lion')) a(aName))
INSERT @Results
SELECT ISNULL(Name, aName) Name
,   CASE WHEN [Count] IS NULL THEN 1 ELSE 1 + [Count] END [Count] 
    FROM animal_csv
        LEFT JOIN animals
            ON Name = animal_csv.aName

SELECT * FROM @results

Or, if you just want to use a temporary table, you can build it like this (temp tables are deleted when the connection is released/closed or when they're explicitly dropped):

;WITH animal_csv AS (SELECT * FROM (VALUES('horse'),('giraffe'), ('lion')) a(aName))
SELECT ISNULL(Name, aName) Name
,   CASE WHEN [Count] IS NULL THEN 1 ELSE 1 + [Count] END [Count] 
    INTO #results
    FROM animal_csv
        LEFT JOIN animals
            ON Name = animal_csv.aName

SELECT * FROM #results

Upvotes: 1

Related Questions