using select to update a record in the same table

[ETOMBS]

I'm trying to insert a key value into the last field in the row (new_lead.)

This is based on data that we have collected over time from clients. I keep having issues getting it to work. I'm not sure how to code it at this point, based on the error

'Error Code : 1248
Every derived table must have its own alias"


UPDATE (SELECT new_lead
FROM client_testc
LEFT OUTER JOIN lead_infoc
ON client_testc.First_Name=lead_infoc.fname
AND client_testc.Address1=lead_infoc.address
AND client_testc.City=lead_infoc.city
AND client_testc.State=lead_infoc.state
WHERE lead_infoc.fname IS NULL
AND lead_infoc.address IS NULL
AND lead_infoc.city IS NULL
AND lead_infoc.state IS NULL)
SET new_lead='y';

 

[PWise]

how about

UPDATE client_testc
set new_lead = 'y'
From (
      SELECT     new_lead
      FROM         client_testc 
      LEFT OUTER JOIN lead_infoc 
      ON client_testc.First_Name = lead_infoc.fname 
      AND client_testc.Address1 = lead_infoc.address 
      AND client_testc.City = lead_infoc.city 
      AND client_testc.State = lead_infoc.state
      WHERE     lead_infoc.fname IS NULL 
      AND lead_infoc.address IS NULL 
      AND lead_infoc.city IS NULL 
      AND lead_infoc.state IS NULL
      ) client_testc

 

[SQLBill]

Here's what is happening and why PWise's suggestion will work. The syntax is:

UPDATE tablename

But you aren't using a tablename, you are using a SELECT...that doesn't work. However, by putting the SELECT in parenthesis, what you are really doing is creating a table on the fly (a derived table). When you alias it, as PWise does, then the statement uses the derived tablename (outside the parenthesis) and 'translates' it by SQL Server as:

UPDATE client_testc

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLSister]

Try:

UPDATE  
SET new_lead='y'
FROM client_testc     
LEFT OUTER JOIN lead_infoc    
  ON client_testc.First_Name=lead_infoc.fname    
  AND client_testc.Address1=lead_infoc.address    
  AND client_testc.City=lead_infoc.city    
  AND client_testc.State=lead_infoc.state        
WHERE lead_infoc.fname IS NULL        
  AND lead_infoc.address IS NULL        
  AND lead_infoc.city IS NULL        
  AND lead_infoc.state IS NULL

"NOTHING is more important in a database than integrity." ESquared