CODEWITHSUNDEEP
sqlsql-server
Maverick
Maverick

Reputation: 37

SQL Update a Column From a Select

I want to update 1 column (loadStatus) in the Loads table based on if another column (IBLoad) from a joined table is NULL. The end result when running my statement has 0 rows affected. I'm not sure what's wrong here but maybe my WHERE clause is incorrect?

This is the first time I've tried to update from a select so trying to figure it out :)

UPDATE Loads SET loadStatus = 'SCHEDULED' 
FROM (
    Select L.OID as [LoadID], T.IBLoad, L.loadStatus
    From [Loads] L left join [Transaction] T on L.OID = T.IBLoad
    Where T.IBLoad is null and load_type = 1 ) i
WHERE  Loads.loadStatus = null

Upvotes: 1

Views: 5684

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You don't need to update from a select in this case. Here is another way to write the query:

UPDATE l
    SET loadStatus = 'SCHEDULED' 
FROM Loads l
WHERE l.loadStatus IS NULL AND
      l.load_type = 1 AND 
      NOT EXISTS (SELECT 1 FROM [Transaction] t WHERE l.OID = t.IBLoad);

Upvotes: 0

Ven
Ven

Reputation: 2014

you do not need subquery, And use is null instead of = null

UPDATE L
SET loadStatus = 'SCHEDULED'
    From [Loads] L left join [Transaction] T on L.OID = T.IBLoad
    Where T.IBLoad is null and load_type = 1 
    and  L.loadStatus  is  null

or use Loads.loadStatus = '' for empty string

Upvotes: 4

Related Questions