sql 更新(帮帮我) [英] sql update (help me )
问题描述
我有 3 张桌子:
<前>tbl_indicatorgrp_nbr, sect_nbr, 指示1 100 人2 101秒<前>tbl_groupgrp_id、grp_nbr、sect_nbr、指示333 1 100 一个555 1 100 羽444 2 101 秒222 2 101 年
这里(在tbl_group中)grp_id是主键
这里(在tbl_order中)grp_id是tbl_group中grp_id的外键.
在表 tbl_indiactor 中，对于一组 grp_nbr 和 sect_nbr 有一个指标，对于同一组 grp_nbr 和 sect_nbr 有一个正确的指标(555,1, 100, p) 和一个垃圾指标(333, 1,100, a) 在表 tbl_group 中，但是这两个 grp_id s(333, 555) 都存在于表 tbl_orders 中.
现在我需要更新 tbl_order 表，这样垃圾 grp_id 应该被正确的 grp_id 替换
输出应该是:
<前>tbl_ordersorder_id, grp_id5000 5555001 5555002 5555003 5555004 4445005 4445006 444这里有一个小改动
tbl_indicator
grp_nbr、sect_nbr、指示
01 100 p
02 101 秒tbl_group
grp_id、grp_nbr、sect_nbr、指示
333 01 100 一个
555 01 100 p
444 02 101 秒222 2 101 岁这里(在 tbl_group 中)grp_id 是主键
组表 (222, 22, 101, y) 中的垃圾数据(indicat) grp_nbr 有一个字符长度但 tbl_indicat 中的 grp_nbr 有两个字符长度...我们该如何处理??
首先弄清楚哪些记录需要更新:
选择 *来自 tbl_order o内连接 tbl_group g ong.grp_id = o.grp_id内连接 tbl_indicator i oni.grp_nbr = g.grp_nbr和 i.sect_nbr = g.sect_nbr在哪里g.indicat != i.indicat现在，修改查询以使用正确的 grp_id 更新这些记录.请注意，我使用别名g2"向 tbl_group 表添加了一个额外的连接.这将是正确的组.
更新集合o.grp_id = g2.grp_id来自 tbl_order o内连接 tbl_group g ong.grp_id = o.grp_id内连接 tbl_indicator i oni.grp_nbr = g.grp_nbr和 i.sect_nbr = g.sect_nbr内连接 tbl_group g2 ong2.grp_nbr = i.grp_nbr和 g2.sect_nbr = i.sect_nbr和 g2.indicat = i.indicat在哪里g.indicat != i.indicat请注意，由于 tbl_group g2 上的内连接，如果不存在任何 tbl_group 记录，其中 indicat 与其关联的 tbl_indicator 记录的 indicat 值匹配，则不会更新记录.
I have 3 tables:
tbl_indicator grp_nbr, sect_nbr, indicat 1 100 p 2 101 s
tbl_group grp_id, grp_nbr, sect_nbr, indicat 333 1 100 a 555 1 100 p 444 2 101 s 222 2 101 y
Here (in tbl_group) grp_id is Primary Key
tbl_order order_id, grp_id 5000 333 5001 555 5002 555 5003 555 5004 444 5005 444 5006 222
Here (in tbl_order) grp_id is a Foreign Key to grp_id in tbl_group.
In table tbl_indiactor, for one set of grp_nbr and sect_nbr there is an indicat, for the same set of grp_nbr and sect_nbr there is a correct indicat(555,1, 100, p) and a junk indicat(333, 1, 100, a) in table tbl_group, but both these grp_id s(333, 555) are present in table tbl_orders.
Now i need to update tbl_order table in such a way that the junk grp_id s should be replaced with correct grp_id s
The output should like:
tbl_orders order_id, grp_id 5000 555 5001 555 5002 555 5003 555 5004 444 5005 444 5006 444
here is a small change
tbl_indicator
grp_nbr, sect_nbr, indicat
01 100 p
02 101 s
tbl_group
grp_id, grp_nbr, sect_nbr, indicat
333 01 100 a
555 01 100 p
444 02 101 s
222 2 101 y
Here (in tbl_group) grp_id is Primary Key
the junk data(indicat) in group table (222, 22, 101, y) the grp_nbr has one character length but the grp_nbr in tbl_indicat has two character length... how can we handle this??
First, figure out which records need to be updated:
select *
from tbl_order o
inner join tbl_group g on
g.grp_id = o.grp_id
inner join tbl_indicator i on
i.grp_nbr = g.grp_nbr
and i.sect_nbr = g.sect_nbr
where
g.indicat != i.indicat
Now, modify the query to update those records with the correct grp_id. Notice that I've added an extra join to the tbl_group table with an alias of "g2". This will be the correct group.
update o set
o.grp_id = g2.grp_id
from tbl_order o
inner join tbl_group g on
g.grp_id = o.grp_id
inner join tbl_indicator i on
i.grp_nbr = g.grp_nbr
and i.sect_nbr = g.sect_nbr
inner join tbl_group g2 on
g2.grp_nbr = i.grp_nbr
and g2.sect_nbr = i.sect_nbr
and g2.indicat = i.indicat
where
g.indicat != i.indicat
Note that due to the inner join on tbl_group g2, the records will not be updated if there does not exist any tbl_group record where indicat matches the indicat value of its associated tbl_indicator record.
这篇关于sql 更新(帮帮我)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
