CODEWITHSUNDEEP
sqlsql-server
Travis
Travis

Reputation: 13

Multi-part Identifier could not be bound in where statement

I am trying to write a basic query that will change a value if data from two tables meets the right criteria. Selecting the data works fine with the where statement I'm using, but trying to Set the value returns the error "The multipart Identifier "imitmidx_sql.item_desc_1" could not be bound.

select 
   comp_item_no, 
   qty_per_par, 
   item_desc_1
from 
   bmprdstr_sql
Left Outer Join
   imitmidx_sql 
   on 
      bmprdstr_sql.comp_item_no = imitmidx_sql.item_no
update 
   bmprdstr_sql 
set 
    qty_per_par = '0' 
    where  
        bmprdstr_sql.comp_item_no like '68%' and
        imitmidx_sql.item_desc_1 like 'WIRE,%'

Using the below query, 2300 rows are affected, I would like to change those affected rows so that qty_per_par is 0

select comp_item_no, qty_per_par, item_desc_1
from bmprdstr_sql
Left Outer Join imitmidx_sql
on bmprdstr_sql.comp_item_no = imitmidx_sql.item_no
where comp_item_no like '68%' and item_desc_1 like 'WIRE,%'

Upvotes: 1

Views: 295

Answers (2)

James L.
James L.

Reputation: 9451

You would use a select statement to determine how many records would be updated. But to do the update, you need to write the update statement so that it includes all of the criteria that you wrote in your select statement.

This includes both tables in the from clause, with aliases x and y, which makes it easy to reference them throughout the rest of the statement.

update x
set    x.qty_per_par = 0
from   bmprdstr_sql x left outer join imitmidx_sql y on x.comp_item_no = y.item_no
where  x.comp_item_no like '68%'
  and  y.item_desc_1 like 'wire,%'

Upvotes: 1

Shawn
Shawn

Reputation: 4786

Since you are using SQL Server 2008, you can do this with a CTE that easily limits the records you want to update.

NOTE: I changed the way you were preforming your JOIN. You are doing a LEFT JOIN and then filtering with a WHERE. This is pretty much the same as just moving that WHERE condition up into the JOIN and switching to an INNER JOIN.

SETUP

CREATE TABLE t1 ( id int, words varchar(10) ) ;
CREATE TABLE t2 ( id int identity, t1ID int, morewords varchar(10)) ;

INSERT INTO t1 (id, words )
VALUES (1,'asfd'), (2, 'jklsemi'),(3,'qwerty') ;

INSERT INTO t2 ( t1ID, morewords )
VALUES (1,'fdsa'),(1,'qq'),(3,'yy');

That gives us:

SELECT * 
FROM t1
INNER JOIN t2 ON t1.id = t2.t1id
    AND t2.morewords = 'fdsa'

id | words | id | t1ID | morewords
-: | :---- | -: | ---: | :--------
 1 | asfd  |  1 |    1 | fdsa

And to do our UPDATE:

; WITH cte1 AS (
    SELECT t1.id, t1.words 
    FROM t1
    INNER JOIN t2 ON t1.id = t2.t1id  
        AND t2.morewords = 'fdsa'
    WHERE t1.words LIKE 'a%'
)
UPDATE cte1
SET cte1.words = 'new'
;

Which then gives us:

SELECT * FROM t1

id | words  
-: | :------
 1 | new    
 2 | jklsemi
 3 | qwerty

db<>fiddle here

Upvotes: 0

Related Questions