Update data from Table2 to Table1 with conditions

Question

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 2-Nov-15 23:11pm

Member 10558090

Updated 3-Nov-15 0:14am

Tomas Takac

v3

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**D V L** · Answer 1 · 2015-11-03T01:52:00

Solution 2

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

Posted 3-Nov-15 1:52am

D V L

Updated 3-Nov-15 1:55am

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.

xszaboj · Answer 2 · 2015-11-03T01:01:00

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