update new_table set Y2 = 555 --- any value or another field: set Y2 = prod_id
where class_id in (
select DISTINCT class_id from new_table t1 where substring(class_id FROM 1 FOR 6) in (select DISTINCT parent_id from new_table t2)
--COMMENT ... in ('000077', '000088') --COMMENT: ... and char_length(parent_id)>6
order by parent_id )
... commit
NOTE: set fieldxxx = ( .... )
SET FieldXXXX = ( SELECT FIRST 1 XXX from ZZZZ ... ) ==> 1 record just SET FieldXXXX = ( SELECT FIRST 1 SKIP 2 XXX from ZZZZ ... ) ==> 1 record just, jump 2 record
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3
note: The use of "IN" (list...) is not advisable due to the number of items that can be listed in a select!
perhaps it would be more interesting to use "CTE - COMMON TABLE RECURSIVE" technique to select the records and use in "UPDATE"
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3
YOU HAVE ALL VALUE already in CLASS_ID, then, you can just to do a "WHERE" in this field
select XXXX from ZZZZ where class_id = a value
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3
1.先获取对应的product-id: select t2.product_id ,t1.product_id,t1.class_id,length(t2.class_id) t2len from Products t1 left join Products t2 on left(t1.class_id,length(t2.class_id))=t2.class_id
--第一级 update products set products.y2=p2.product_id from products inner join products as p2 on LEFT(products.class_id,6)=p2.class_id
--第二级 update products set products.y3=p2.product_id from products inner join productss as p2 on LEFT(products.class_id,12)=p2.class_id where len(products.class_id)>6 --第三级 update products set products.y4=p2.product_id from products inner join productss as p2 on LEFT(products.class_id,18)=p2.class_id where len(products.class_id)>12 --第四级 update products set products.y5=p2.product_id from products inner join productss as p2 on LEFT(products.class_id,24)=p2.class_id where len(products.class_id)>18 --第五级 update products set products.y6=p2.product_id from products inner join productss as p2 on LEFT(products.class_id,30)=p2.class_id where len(products.class_id)>24
--组装 需要依次执行下方4个update update products set y6=''
select * from products
update products set y6=Y1+Y2 where Y2<>'' update products set y6=Y6+'-'+Y3 where Y3<>'' update products set y6=Y6+'-'+Y4 where Y4<>'' update products set y6=Y6+'-'+Y5 where Y5<>''