Update table from another table SQL

Question

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

Please tag your question with the database you are using. You should also show sample data for table1 and table2. — Gordon Linoff
– Gordon Linoff, Commented Nov 7, 2014 at 15:40
sorry.... a data example is: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 — user3641134
– user3641134, Commented Nov 7, 2014 at 15:48
@user3641134, sample data would be far better off as an edit to your original post - please click the word 'edit' under the question, and put the values there. — AHiggins
– AHiggins, Commented Nov 7, 2014 at 15:51

Gordon Linoff · Accepted Answer · 2014-11-07 15:42:36Z

2

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;

answered Nov 7, 2014 at 15:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Benoît S Over a year ago

will your query work in Oracle ?

gunr2171 · Accepted Answer · 2014-11-07 16:51:02Z

0

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

edited Nov 7, 2014 at 16:51

gunr2171

17.7k26 gold badges68 silver badges102 bronze badges

answered Nov 7, 2014 at 16:27

Rahul Sharma

4533 silver badges11 bronze badges

1 Comment

gunr2171 Over a year ago

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

Collectives™ on Stack Overflow

Update table from another table SQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related