How to use AS to substitute a value when inserting based on another record

Question

I want to add missing records by copying other records.

If a record is missing Field2 value of '033', I want to copy a record that has Field2 = '012' and just replace it with '033'

My only error is using AS. Appears I cannot substitute a value.
Field1 and Field2 are Key.

Insert into Table (Field1,'033' as Field2,Field3)
select * from (select * from Table) as tmp)
where not exist (select TOP 1 Field2 from table where Field2='012')

Answer 1

Try:

;with cte as (select Field1, '033' as Field2, Field3 from myTable t where not exists (select 1 from myTable t2 where Field2 = '012') -- what about field1 - do we want correlated subquery, do we want just one row or many?

insert into table (field1, field2, field3) -- first comment insert portion and verify you got expected results before actual insert
select Field1, Field2, Field3 from cte

Answer 2

Hi @RobertLassetter-8330
Insert into table( column list )
For column list ,we can't use '033' as Field2 here.
We can use '033' as Field2 in the select clause.Please check the code that Viorel commented above.

Best Regards
LiHong