SQL Server Insert value from other table and Multiply 2 colums in a Table SQL [closed]

62lalag4  于 2023-04-28  发布在  其他
关注(0)|答案(2)|浏览(132)

Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 2 days ago.
Improve this question

I have a table with 5 columns enter image description here like this. The 1st, 2nd and 3rd column was entered from keyboard The 4th column is insert from other table The 5th column = 2nd column * 3rd column. How can I insert this? My code is wrong can u fix that. Thanks everyone

How can I insert this? My code is wrong can u fix that. Thanks everyone

rqenqsqc

rqenqsqc1#

You have:

values 
(
  2,
  4,
  3,
  (Select dongia from hanghoa),
  (select soluong * dongia from chitietdonhang),
  (
    1,
    5,
    2,
    (Select dongia from hanghoa),
    (select soluong * dongia from chitietdonhang),
    (
      4 ...

The indention shows that you are getting deeper and deeper, because you forgot the closing parentheses and commas after each tuple. This is the code corrected:

values 
(
  2,
  4,
  3,
  (Select dongia from hanghoa),
  (select soluong * dongia from chitietdonhang),
),
(
  1,
  5,
  2,
  (Select dongia from hanghoa),
  (select soluong * dongia from chitietdonhang),
),
(
  4, ...

This would make the syntax valid. It is tuples of five values that your are inserting. The first tuple consists of the values 2, 4, and 3, and the value in dongia from hanghoa, and the result of soluong * dongia from chitietdonhang. So you expect the two subqueries to return one value. Do the two tables have only one row each? Otherwise this cannot work, because each subquery represents one value in the insert statement. If there is more than one row in the tables, you would have to add a where clause to pick the row you want or aggregate the table data to a single result row.

Update

I've tried Google Translate. The table you are inserting to is Chi Tiết Đơn Hàng = order details? With Số Lượng = quantity, Đơn Giá = unit price, Thanh Toán = total price? And Hàng Hóa is the product table and MaHH = Mã Hàng Hóa = product code?

In that case you want to select the unit price from the product table. But you must do that for the product you are inserting:

insert into chitietdonhang (madh, mahh, soluong, donia, tanhtien) 
values 
(
  2,
  4, /* product code */
  3, /* quantity */
  (Select dongia from hanghoa where mahh = 4),
  (Select dongia from hanghoa where mahh = 4) * 3
), ...

But then, if the total price is the result of two columns in the same table row, you should not store it. If you store quantity (Số Lượng) = 5 and unit price (Đơn Giá) = 10, then the total (Thanh Toán) must be 50, correct? Storing this would mean redundancy and a dependency of one column from others. That must not be. Database normalization forbids this. I suggest you remove this column from your table. You always quickly calculate it when needed. Or you make this a computed column that you never fill with a value, because it gets its value automatically from the other columns:

CREATE TABLE chitietdonhang
(
  ...,
  TanhToan AS SoLuong * DonGia
);

The statement would then be reduced to:

insert into chitietdonhang (madh, mahh, soluong, donia) 
values (2, 4, 3, (Select dongia from hanghoa where mahh = 4)),
       (1, 5, 2, (Select dongia from hanghoa where mahh = 5)),
       (4, 1, 1, (Select dongia from hanghoa where mahh = 1)),
       (3, 8, 1, (Select dongia from hanghoa where mahh = 8)),
       (5, 1, 5, (Select dongia from hanghoa where mahh = 1));
kninwzqo

kninwzqo2#

You must specify the following

1.filter two tables

2.Which record should be extracted?(I temporary use Top 1)

3.numbers is hardcode(if isn't you must use decclare exmaple decclare @Madh int)

insert into chiTietDonHang(Madh,Mahh,soluong,Dongia,ThanhTian)

select 2,4,3,(select Top 1 Dongia from  HangDoa /*where ??*/),(select Top 1 soluong * Dongia from chiTietDonHang /*where ??*/)
Union all

select 1,5,2,(select Top 1 Dongia from  HangDoa /*where ??*/),(select Top 1 soluong * Dongia from chiTietDonHang /*where ??*/)
Union all
select 4,1,1,(select Top 1 Dongia from  HangDoa /*where ??*/),(select Top 1 soluong * Dongia from chiTietDonHang /*where ??*/)
Union all

select 3,8,1,(select Top 1 Dongia from  HangDoa /*where ??*/),(select Top 1 soluong * Dongia from chiTietDonHang /*where ??*/)

Union all

select 5,1,5,(select Top 1 Dongia from  HangDoa /*where ??*/),(select Top 1 soluong * Dongia from chiTietDonHang /*where ??*/)

create table :

create table chiTietDonHang (Madh int,Mahh int,soluong int,Dongia int,ThanhTian int)

create table HangDoa (Dongia int)

相关问题