从条件更新Table2到Table1的数据 [英] Update data from Table2 to Table1 with conditions
问题描述
大家好,
我的SQL Server中有2个表。
First Table
< pre lang =text> SKU名称描述供货情况价格
aa abcdfghilmn 10 15,10
bb abcdfghilmn 45 18,90
cc abcdfghilmn 90 40,00
第二张表
<前一行=文字> SKU名称描述可用性价格
at abcdfghilmn 90 25,10
bg abcdfghilmn 00 12,90
cm abcdfghilmn 01 38,00
我想使用SKU像WHERE条件更新第二桌到第一桌的可用性和价格。
首先,我使用数组隔离了SKU。为了在我升级数据时将它们用作条件。
这是我的代码:
string connString = 数据源= 192.168。 50.20,1433;网络库= DBMSSOCN;初始目录= Gestionale;用户ID = *;密码= *;
SqlConnection connessione = new SqlConnection(connString);
string codice_giacenza = 选择Codice_prodotto_del_produttore_ManufacturerSKU来自dbo.ListinoNetline;
SqlCommand cmd = new SqlCommand(codice_giacenza,connessione);
ArrayList valori = new ArrayList();
SqlDataReader阅读器;
connessione.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
valori.Add(reader [ 0 跨度>]的ToString());
}
foreach ( var item in valori)
{
string query = < span class =code-string> SELECT MIN(Price),FROM FROM Table2 WHERE SKU =' + item.ToString()+ < span class =code-string>';
}
到达这一点我无法前进。
但是,有约束。在第二个表中有可能存在具有相同SKU的记录。所以,我想
只选择具有可用性的MIN价格。但是,可用性必须是> = 1,否则我选择第二好的价格。
这是最好的方法吗?
试试以下
也告诉我,如果我忘记了什么。
update t1
set t1。可用性= 案例 何时(t2.Availability> = 1 和 t2.Price< t1.Price)然后 t2.Availability else t1.Availability end ,
t1.Price = case 何时(t2.Availability> = 1 和 t2.Price< t1.Price)然后 t2.Price else t1.Price end
来自 Table1 as t1
内部 加入表2 t2
on t1.SKU = t2.SKU
这是您获取所需数据的方式在数据库上。如果您有任何问题,请询问;)
开始 tran
drop table #table2
创建 表#table2(
sku varchar ( 20 ),
avail int ,
price int
)
insert 到#table2 值(' a', 1 , 18 )
insert into #table2 values (' b', 1 , 20 )
insert into #table2 values (' c', 1 , 15 )
插入 进入#table2 values (' d', 0 , 25 )
insert into #table2 values (' a', 20 , 13 )
insert 到#table2 值(' a', 8 , 9 )
insert into #table2 values (' a', 9 , 17 )
insert into #table2 值(' b', 0 , 25 )
选择 * 来自#table2
选择 sku,min(价格)来自#table2 where avail> = 1
group by sku
select grouped.sku,t.avail,grouped.price 来自#table2 t
left join (
选择 sku,min(价格)为价格来自#table2 其中 avail> = 1
group 按 sku
) as 分组
t.sku = grouped.sku 和 t.price = grouped.price
其中 grouped.sku 不 null
- 插入#table1(sku,avail,price)以上选择
rollback 移植跨度>
Hi everyone,
I have 2 table in my SQL Server.
First Table
SKU Name Description Availability Price
a a abcdfghilmn 10 15,10
b b abcdfghilmn 45 18,90
c c abcdfghilmn 90 40,00
Second Table
SKU Name Description Availability Price
a t abcdfghilmn 90 25,10
b g abcdfghilmn 00 12,90
c m abcdfghilmn 01 38,00
I want to update Availability and Price from Second Table to the First Table using SKU like "WHERE" conditions.
First of all, I isolated the SKU using an array. In order to use them as a condition when I go to upgrade the data.
This is my code:
string connString = "Data Source=192.168.50.20,1433; Network Library=DBMSSOCN; Initial Catalog = Gestionale; User ID=*; Password=*";
SqlConnection connessione = new SqlConnection(connString);
string codice_giacenza = "SELECT Codice_prodotto_del_produttore_ManufacturerSKU FROM dbo.ListinoNetline";
SqlCommand cmd = new SqlCommand(codice_giacenza, connessione);
ArrayList valori = new ArrayList();
SqlDataReader reader;
connessione.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
valori.Add(reader[0].ToString());
}
foreach(var item in valori)
{
string query = "SELECT MIN(Price), Availability FROM Table2 WHERE SKU = '" + item.ToString() + "'";
}
Arriving a this point I could not go forward.
But, there is a constraint. There are the possibility that in the second table there are records with the same SKU. So, I want to
select only the MIN price with its Availability. But, Availability must to is >= 1, else i choose the "second best price".
Which is the best way to do it ?
Try following
also tell me if i forgot something.
update t1 set t1.Availability = case when (t2.Availability>=1 and t2.Price<t1.Price) then t2.Availability else t1.Availability end, t1.Price = case when (t2.Availability>=1 and t2.Price<t1.Price) then t2.Price else t1.Price end from Table1 as t1 inner join Table2 as t2 on t1.SKU=t2.SKU
this is how you will get data you need on database. if you have any questions just ask ;)
begin tran drop table #table2 create table #table2( sku varchar(20), avail int, price int ) insert into #table2 values('a', 1, 18) insert into #table2 values('b', 1, 20) insert into #table2 values('c', 1, 15) insert into #table2 values('d', 0, 25) insert into #table2 values('a', 20, 13) insert into #table2 values('a', 8, 9) insert into #table2 values('a', 9, 17) insert into #table2 values('b', 0, 25) select * from #table2 select sku, min(price) from #table2 where avail >= 1 group by sku select grouped.sku, t.avail, grouped.price from #table2 t left join ( select sku, min(price) as price from #table2 where avail >= 1 group by sku ) as grouped on t.sku = grouped.sku and t.price = grouped.price where grouped.sku is not null --insert into #table1 (sku, avail, price) above select rollback tran
这篇关于从条件更新Table2到Table1的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!