Need to Update a column within a function

Don't forget to wrap up the code in a transaction to ensure consistent behaviour.

You can't update a base table from a function, unfortunately. This is going to prove a bit trickier. It is certainly possible to do this but a simpler alternative would get a temporary table of the barcodes first, and then assign them within the select statement.--all wrapped within a transaction.

Since your select statement might return more than one record, the fact that

You can't update a base table from a function

is actually a very good news as otherwise, it would allow you to update the base table as many times as there are records affected by the insert istead of doing it only once. This would be horrible. Here is the primitive sceleton of how you can do it. It should work if you have SQL Server 2005 or better.

declare @current_code int;
declare @final_code;

begin tran;

select @current_code = max(barcode_column) from dbo.Barcode;

insert into SomeTable ([name], docType, barcode)
select 
    [name], DocType, 
    @current_code + row_number() over (order by some_column)
    from SomeOtherTable 
    where Criteria = DesiredCriteria;

-- assuming that the number of affected rows matches the @@rowcount

set @final_code = @@rowcount + @current_code + 1;

update dbo.Barcode set barcode_column = @final_code;

commit tran;
go

Oleg

Hi, I am not fully understand about your questions. May be that is what you want -

INSERT INTO T1(name, type, barcode)
SELECT T2.Name, T2. Type, T3.Barcode
FROM Table2 as T2 inner join Table3 as T3 
ON T2.Type = T3.Type 
WHERE T3.Criteria = 'xxxxx' (or something Tx.*)