CODEWITHSUNDEEP
sql-servert-sqlsql-update
Adam
Adam

Reputation: 6122

Update multiple rows throws The multi-part identifier could not be bound

I want to update all rows in table [listings] with a new friendlyurl value. Instead of looping through all records I thought I could also do it like so:

;WITH vars1 AS (
SELECT title FROM listings where listingtype=2 order by createdate desc)
UPDATE listings set friendlyurl=REPLACE(LOWER(vars1.title),' ','-')

But I get the error on the last line:

The multi-part identifier vars1.title could not be bound.

How can I correct the above statement to work as I intend?

Upvotes: 0

Views: 116

Answers (1)

Taryn
Taryn

Reputation: 247680

If you want to use a CTE to update then you could use:

;with vars1 as
(
  select title, friendlyUrl
  from listings
  where listingtype=2 
) 
update vars1 
set friendlyurl = REPLACE(LOWER(vars1.title),' ','-');

But you can do this without a CTE:

update listings
set friendlyurl = REPLACE(LOWER(title),' ','-')
where listingtype=2;

See Demo with both queries

Upvotes: 2

Related Questions