求一多对多关系下的更新语句!
表 a:
UUIDAPP_LINE_ID
87219513131
表 ca:
ASSET_IDAPP_LINE_TF_IDAPP_LINE_TK_ID
87219 4943860
87219 5131310
表 ar:
RELATION_IDASSET_ID EVENT_TYPE
494386872191507
494386872191507
513198872191508
513198872191508
513131872191507
513131872191507
表间关系说明:
1.表a的uuid是表a是主键,同时作为表ar与ca的外键
2.表ca的APP_LINE_TF_ID,APP_LINE_TK_ID,表 ar的 RELATION_ID 实质都是表a的APP_LINE_ID,只是表a只记录最后一次发生的 APP_LINE_ID
3.ar中 EVENT_TYPE = 1507 的要对应表ca中 APP_LINE_TF_ID
4.ar中 EVENT_TYPE = 1508 的要对应表ca中 APP_LINE_TK_ID
以上数据有异常,需要的结果是让表ar变成如下结果:
ASSET_IDAPP_LINE_TF_IDAPP_LINE_TK_ID
87219 494386513198
87219 5131310
[解决办法]
表不少,怎么连个主键都没有?
declare @表a table (UUID int,APP_LINE_ID int)insert into @表aselect 87219,513131declare @表ca table (ASSET_ID int,APP_LINE_TF_ID int,APP_LINE_TK_ID int)insert into @表caselect 87219,494386,0 union allselect 87219,513131,0declare @表ar table (RELATION_ID int,ASSET_ID int,EVENT_TYPE int)insert into @表arselect 494386,87219,1507 union allselect 494386,87219,1507 union allselect 513198,87219,1508 union allselect 513198,87219,1508 union allselect 513131,87219,1507 union allselect 513131,87219,1507select * from @表aselect * from @表caselect * from @表ar
[解决办法]
看了一上午,还是不太明白楼主什么意思,猜测楼主的需求代码写出来了,看看吧~
declare @表a table (UUID int,APP_LINE_ID int)insert into @表aselect 87219,513131declare @表ca table (ASSET_ID int,APP_LINE_TF_ID int,APP_LINE_TK_ID int)insert into @表caselect 87219,494386,0 union allselect 87219,513131,0declare @表ar table (RELATION_ID int,ASSET_ID int,EVENT_TYPE int)insert into @表arselect 494386,87219,1507 union allselect 494386,87219,1507 union allselect 513198,87219,1508 union allselect 513198,87219,1508 union allselect 513131,87219,1507 union allselect 513131,87219,1507select * from @表aselect * from @表caselect * from @表arupdate @表ca set APP_LINE_TF_ID=(select top 1 relation_id from @表ar where EVENT_TYPE='1507') where APP_LINE_TF_ID!=(select APP_LINE_ID from @表a)update @表ca set APP_LINE_TK_ID=(select top 1 relation_id from @表ar where EVENT_TYPE='1508') where APP_LINE_TF_ID!=(select APP_LINE_ID from @表a)select * from @表ca