Dear Developers

SQL, Table CODE has data as following

Code------product
1-----------Mango
2-----------Potato
3-----------Orange

SQL, Table SALE has following data

Code------Product
2-----------
3-----------

I want to fill sale.product column with code.product columns as

Code------Product
2-----------Potato
3-----------Orange

What query should I USE?

Please help

Given relational database normalization, you should not do that. What you do is join Sale and Code tables in your select statement.

Select c.code, c.product from code c inner join sale s on c.code=s.code

However for some reason you do want to update the SALE table, just for sake of it here is SQL that works in MS SQL. Nothing to do with VB.net (though you can execute Non Query)

Update Sale set product=(select top 1 product from Code where code.code=sale.code)

And last, Why won't you call these tables as Product and Sales? A code table with Code column? I am sure you are just a student. No programming professsional will use this naming convention.

Edited 6 Years Ago by padtes: n/a

>What query should I USE?

UPDATE.

UPDATE TAB1 set TAB1.COL1 = TAB2.COL1
FROM TAB1 
INNER JOIN TAB2 ON TAB1.COL2 = TAB2.COL2

Given relational database normalization, you should not do that. What you do is join Sale and Code tables in your select statement.

Select c.code, c.product from code c inner join sale s on c.code=s.code

However for some reason you do want to update the SALE table, just for sake of it here is SQL that works in MS SQL. Nothing to do with VB.net (though you can execute Non Query)

Update Sale set product=(select top 1 product from Code where code.code=sale.code)

And last, Why won't you call these tables as Product and Sales? A code table with Code column? I am sure you are just a student. No programming professsional will use this naming convention.

SELECT top 1 product

In above codes What do you mean by TOP 1 PRODUCT

SELECT top 1 product

In above codes What do you mean by TOP 1 PRODUCT

The way my update query is, it uses sub query. In case of update (and Select as well), when such query is used, it must return 0 or 1 record. If the subquery returns more than 1 record then it bombs at run time. To make sure it will work irrespective of your data, I use Top 1. That way it will return no or 1 record.

Given relational database normalization, you should not do that. What you do is join Sale and Code tables in your select statement.
However for some reason you do want to update the SALE table, just for sake of it here is SQL that works in MS SQL. Nothing to do with VB.net (though you can execute Non Query)

Update Sale set product=(select top 1 product from Code where code.code=sale.code)

And last, Why won't you call these tables as Product and Sales? A code table with Code column? I am sure you are just a student. No programming professsional will use this naming convention.

The cammad says

Comma, ')' or valid expression continuation expected

This article has been dead for over six months. Start a new discussion instead.