I thought the query was going to be easy, but I was misteken. All I am trying to achieve is: for each row in table 1 that meets specific criteria have 'the next value' from table 2 added to it. There are no columns I can use to link the tables.
I have written this code but it does not work properly if there are more then one row that needs updating.
Expand|Select|Wrap|Line Numbers
- DECLARE @oacseqn varchar(10)
- declare @oac varchar(15)
- --find the max sequence number (so we can use the next row)
- select @oacseqn = max(seqn) from Sciquest_oac where used = 1
- --get next oac from Sciquest_oac
- select @oac = oac from sciquest_oac where seqn = @oacseqn+1
- update [Name_Sub_Record] set
- OAC_number = @oac
- from [Name_Sub_Record] s
- where s.via_SQ = 'false' and
- s.publ_product like 'abcd_%'
- update Sciquest_oac set used = 'true' where seqn = @oacseqn+1