CODEWITHSUNDEEP
sqlsql-server
Diogo Santos
Diogo Santos

Reputation: 830

Update statement with Where Not Exists

I am trying to do the following: Set the status column to 1 when the row in the first table (variable) does not exist in the second one.

I tried this:

update @table1
set status=1
where  NOT EXISTS (select top 1 1 from @table2 where @[email protected])

But this doesn't even compile, not recognizing @table1 in the Where statement.

Must declare the scalar variable "@table1".

Any clue about this?

Upvotes: 1

Views: 4833

Answers (6)

Somendra Kanaujia
Somendra Kanaujia

Reputation: 824

You should use alias name for both table.

DECLARE @TABLE_1 TABLE  (DEPT_NAME VARCHAR(50),DEP_ID INT)

INSERT INTO @TABLE_1(DEPT_NAME,DEP_ID)
SELECT 'IT',1 UNION ALL
SELECT 'HR',2 UNION ALL
SELECT 'ACCOUNT',3 UNION ALL
SELECT 'ADMIN',4 UNION ALL
SELECT 'SALES',5 UNION ALL
SELECT 'CEO',7 


DECLARE @TABLE_2 TABLE  (E_ID INT,EMP_NAME VARCHAR(50),DEP_ID INT)

INSERT INTO @TABLE_2(E_ID,EMP_NAME,DEP_ID)
SELECT 1,'JHON',1 UNION ALL
SELECT 2,'LITA',2 UNION ALL
SELECT 3,'MATT',1 UNION ALL
SELECT 4,'JEFF',1 UNION ALL
SELECT 5,'BROCK',2 UNION ALL
SELECT 6,'BOB',5 UNION ALL
SELECT 7,'SAM',4 UNION ALL
SELECT 8,'DAVID',3 UNION ALL
SELECT 9,'JACK',1 UNION ALL
SELECT 10,'GARY',4 UNION ALL
SELECT 11,'DONALD',6 



SELECT * FROM @TABLE_1 A WHERE  NOT EXISTS (SELECT DEP_ID  FROM @TABLE_2 B WHERE A.DEP_ID=B.DEP_ID )

Upvotes: 0

Waleed Naveed
Waleed Naveed

Reputation: 2381

You have to declare table1 and table2 variables

DECLARE @table1 YOUR_TABLE1_NAME;
DECLARE @table2 YOUR_TABLE2_NAME;

update @table1
set status=1
where NOT EXISTS (select top 1 from @table2 where @[email protected])

Upvotes: 0

fiveobjects
fiveobjects

Reputation: 4289

There are multiple ways - inner query with NOT IN and NOT EXISTS and JOIN query:

update tab1 set status = 1 where name not in (select name from tab2); 
update tab1 set status = 1 where not exists (select 1 from tab2 where tab1.name=tab2.name);
update tab1 set status = 1 from tab1 left outer join tab2 on tab1.name = tab2.name where tab2.name is null;

Sample schema to run above queries;

create table tab1(name varchar(30), status int);
create table tab2(name varchar(30));

insert into tab1 values('a', 5);
insert into tab1 values('b', 6);
insert into tab1 values('c', 7);
insert into tab1 values('d', 8);

insert into tab2 values('a'); insert into tab2 values('d');

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your approach is fine. You just need table aliases because the @ is used to in SQL Server to represent variables (scalars or tables) and is hence problematic for aliases:

update t1
    set status = 1
    from @table1 t1
    where not exists (select 1 from @table2 t2 where t2.foo = t1.foo);

Note that the top 1 is unnecessary in the subquery.

Upvotes: 5

Rob Streeting
Rob Streeting

Reputation: 1735

You can do this kind of thing by joining the two tables with a LEFT JOIN and checking the right side for NULL:

UPDATE t1
SET t1.status=1
FROM @table1 t1
LEFT JOIN @table2 t2
    ON t1.foo = t2.foo
WHERE t2.foo IS NULL

The specific error you got is because you haven't got a statement declaring @table1 as a table variable, like DECLARE @table1 TABLE (foo int) for example. If table1 is not a variable, you don't need the @.

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

no need any top inside scaler query

update @table1
set status=1
where  NOT EXISTS (select 1 from @table2 where @[email protected])

cause exists return boolean

you could use below query

update  @table1 
    set status=1
 where @table1.foo not in ( select foo from @table2 where foo is not null)

Upvotes: 0

Related Questions