CODEWITHSUNDEEP
sqlt-sql
user3641134
user3641134

Reputation: 1

Update table from another table SQL

Ive hit a wall with what seems a simple procedure.

Update a table from another table, heres where i am:

DECLARE @oldcode  varchar(50)
DECLARE @newcode  varchar(50)


SET @oldcode = table1.OLDCODE
SET @newcode = table1.NEWCODE


UPDATE table2 SET [CODE] = @NEWCODE WHERE [CODE] = @OLDCODE

this results in: The multi-part identifier "table1.OLDCODE" could not be bound.

cheers :-

the data:

Table1 

record  OLDCODE NEWCODE
1   ZZZALF38    ALF38
2   ZZZALF38.1  ALF38.1
3   ZZZALF38.2  ALF38.2

table2 

record  CODE    
1   ZZZALF38    
2   ZZZALF38.1  
3   ZZZALF38.2

wish to change table 2 to: 

record  CODE    
1   ALF38   
2   ALF38.1 
3   ALF38.2

Upvotes: 0

Views: 86

Answers (2)

Rahul Sharma
Rahul Sharma

Reputation: 453

update table2 set code=table1.newcode from table1  on table2.code=table1.code

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I am guessing you are using SQL Server and this is what you want:

update t2
    set code = t1.newcode
    from table2 t2 join
         table1 t1
         on t2.code = t1.oldcode;

Upvotes: 2

Related Questions