Click here to Skip to main content
15,394,327 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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:
C#
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 ?
Posted
Updated 3-Nov-15 0:14am
v3
Comments
Sinisa Hajnal 3-Nov-15 7:16am
   
Best way is to write SQL UPDATE statement, not loop through tables in the code. SQL is created with data sets in mind, optimized to work fast over many rows...especially since your loop will loose time accessing the database multiple times.

Try following

also tell me if i forgot something.

SQL
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
   
v2
Comments
Member 10558090 3-Nov-15 8:37am
   
I think that you query works, because when I run it on my SQL Management Studio says: "(806 row(s) affected)".

But, if I do:

SELECT * FROM Table1

The column t1.Availability and t1.Price have data "NULL"

Why ?

Edit:

Now, using your query I had this error:
Msg 8114, Level 16, State 5, Line 1

Is a problem to convert data. Because in the first table Price and Availability are VARCHAR and in the second table are DECIMAL(18,4) and INT
D V L 3-Nov-15 12:16pm
   
and as per your question & information given
works well
but you have to add with detail to get accurate answer
D V L 3-Nov-15 12:11pm
   
well, i tried on my server to solve your problem..
query works fine without null
little conversion & some condition changes will works
Member 10558090 4-Nov-15 5:46am
   
I solved conversion problem, but price and availability didn't change.
this is how you will get data you need on database. if you have any questions just ask ;)

SQL
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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900