We help IT Professionals succeed at work.

In mssql what is the best way to update  field1 based on field2 where field3 is search field all in same row

What is the best way to update a field in a row by another field in the same row?

I have a table1 where columns are:   recid, field1, field2, field3  and recid is keyfield
Problem: for every record where field1 is like 'xx%'  then copy contents of field3 into field2

Using Microsoft SQL server Management studio query I tried:

UPDATE table1
    SET field2 = tbl2.field3
    FROM table1 tbl2
    WHERE tbl2.recid = recid and tbl2.field1 LIKE 'xx%'

I got an error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'REQ_MULTIVALUE'.

Any suggestions appreciated.  

Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
UPDATE table1
    SET field2 = tbl2.field3
    FROM table1 tbl1 inner join table2 tbl2 on tbl1.recid = tb12.recid
    WHERE tbl2.field1 LIKE 'xx%'
   
    or more simply
   
UPDATE table1
SET field2 = field3
WHERE field1 LIKE 'xx%'

Author

Commented:
using first suggestion I get error message:
Msg 8154, Level 16, State 1, Line 4
The table 'table1' is ambiguous.

using more simply the error message is:
" Msg 208, Level 16, State 1, Line 1
Invalid object name 'REQ_MULTIVALUE'.  "

note object is not column in table
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
Can you post the exact query tried so that I can fix directly in that..
Have you tried the more simpler version posted:

UPDATE table1
SET field2 = field3
WHERE field1 LIKE 'xx%'

Author

Commented:
***********  this runs in mssql sql server manager , but doesn't have the where field.

create table #table1
(
RequestID int,
field1 varchar(4),
field2 int,
field3 int
)
insert #table1
select 1, 'xx', 4, 3  union all
select 2, 'yy', 4, 3  union all
select 3, 'zz', 4, 3  union all

select * from #table1

update #table1
set field2 = l.field3
from #table1
inner join #table1 l on #table1.RequestID = l.RequestID

select * from #table1

drop table #table1

************   this example below doesn't run
create table #table1
(
RequestID int,
field1 varchar(4),
field2 int,
field3 int
)
insert #table1
select 1, 'xx', 4, 3  union all
select 2, 'yy', 4, 3  union all
select 3, 'zz', 4, 3  union all

select * from #table1

UPDATE #table1
SET field2 = field3
WHERE field1 LIKE 'xx%'

select * from #table1

drop table #table1
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
First UPDATE works since it is updating all records present in the table without checking for any WHERE clause and no. of records updated in the first is 3.

Second UPDATE works only on the WHERE clause specified ie., only with field1 like 'xx%' and hence it updates only one record..
Kindly let me know what exactly you want to achieve..

And the more simplified version of your first query would be

update #table1
set field2 = field3

Author

Commented:
 
Got the following to work, but still can't get a straight forward set or update to work.  

<!--[if gte mso 9]>   Normal  0          false  false  false    EN-US  X-NONE  X-NONE                                       MicrosoftInternetExplorer4                                     <![endif]--><!--[if gte mso 9]>                                                                                                                                                                                                                                                                                    <![endif]--><!--[if gte mso 10]><![endif]-->create table #table1
(
RequestID int,
field1 varchar(4),
field2 int,
field3 int
)
insert #table1
select 1, 'xx', 4, 3  union all
select 2, 'yy', 4, 3  union all
select 3, 'zz', 4, 3  union all
 
select * from #table1
 
update #table1
set field2 = l.field3
from #table1
inner join #table1 l on #table1.RequestID = l.RequestID
where l.field1 = 'xx'
 
select * from #table1
 
droptable #table1

Author

Commented:
try again (boy can't copy formatted stuff),  I got this to work, but not a straight forward update or set


create table #table1
(
RequestID int,
field1 varchar(4),
field2 int,
field3 int
)
insert #table1
select 1, 'xx', 4, 3  union all
select 2, 'yy', 4, 3  union all
select 3, 'zz', 4, 3  union all

select * from #table1

update #table1
set field2 = l.field3
from #table1
inner join #table1 l on #table1.RequestID = l.RequestID
where

select * from #table1

drop table #table1

Author

Commented:
sorry munched the where statement:


create table #table1
(
RequestID int,
field1 varchar(4),
field2 int,
field3 int
)
insert #table1
select 1, 'xx', 4, 3  union all
select 2, 'yy', 4, 3  union all
select 3, 'zz', 4, 3  union all

select * from #table1

update #table1
set field2 = l.field3
from #table1
inner join #table1 l on #table1.RequestID = l.RequestID
where l.field1 = 'xx'

select * from #table1

drop table #table1
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019
Commented:
Instead of using the below self join

update #table1<o:p></o:p>
set field2 = l.field3<o:p></o:p>
from #table1<o:p></o:p>
inner join #table1 l on #table1.RequestID = l.RequestID<o:p></o:p>
where l.field1 = 'xx'<o:p></o:p>

below simple UPDATE should work better..

UPDATE #table1
SET field2 = field3
WHERE field1 LIKE 'xx%'

Author

Commented:
perfect, why didn't it work earlier for me.  Thanks a million



Author

Commented:
Thank you for your significant help
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
there might have been some issues in the application code you might have wrapped it in..
Changing the latest one might have resolved it out..