How do I insert a subquery with multiple results into multiple rows?

Question

I want to add the results of a query into a table. There are multiple values, being inserted into multiple rows that are currently NULLS.

I have the following tables:

CREATE TABLE CAR (
CarID INT NOT NULL PRIMARY KEY,
Make VARCHAR (30) NOT NULL,
Model VARCHAR (30) NOT NULL,
Type VARCHAR (30) NOT NULL,
YearModel INT NOT NULL,
Price VARCHAR (100) NOT NULL,
);

CREATE TABLE [TRANSACTION] (
tID INT NOT NULL PRIMARY KEY,
cID INT NOT NULL FOREIGN KEY REFERENCES CUSTOMER(CID),
CarID INT NOT NULL FOREIGN KEY REFERENCES CAR(CARID),
eID INT NOT NULL FOREIGN KEY REFERENCES EMPLOYEE(EID),
tDate DATE,
PickupDate DATE NOT NULL,
ReturnDate DATE NOT NULL
);

I then had to add a new column:

ALTER TABLE [TRANSACTION]
ADD Amount_Due int;

The following code gives me the results I need:

SELECT Price*DATEDIFF(DAY,PickupDate, ReturnDate)
FROM [TRANSACTION], CAR
WHERE [TRANSACTION].CarID = CAR.CarID

But I don't know how to insert all of the data into my Amount_Due column.

I have tried to use INSERT INTO, but it's telling me I have a syntax error near Amount_Due.

INSERT INTO [TRANSACTION] Amount_Due
SELECT Price*DATEDIFF(DAY,PickupDate, ReturnDate)
FROM CAR, [TRANSACTION]
WHERE CAR.CarID = [TRANSACTION].CarID

I have played around with INSERT INTO and UPDATE and I cannot wrap my head around what I'm doing wrong, or what I am missing.

I'm using SQL SMS 2018

Thank you for any help.

I would advise you not to do this if possible. Better to keep only one source of information and sum it up when you need, rather than keeping two copies — Charlieface
– Charlieface, Commented Jun 22, 2021 at 10:04
FYI SSMS is not the database, and the version of SSMS is usually not relevant. What you are interested in is the result from select @@version. — Dale K
– Dale K, Commented Jun 22, 2021 at 10:12

Stu · Accepted Answer · 2021-06-22 07:37:01Z

2

You are not inserting data you are updating existing rows, so you need to update:

update t set
    t.Amount_Due = c.Price * DateDiff(day, c.PickupDate, c.ReturnDate)
from [transaction] t
join car c on c.carId=t.carId

Notes

Always use proper join syntax, avoid adding tables separated by commas.
Also always alias your tables with meaningful short aliases for readability.
Avoid using reserved words for objects eg transaction - if your table contains more than 1 row then call it transactions and avoid the need to always have to use [] to avoid ambiguity.
SSMS is not SQL Server, SSMS is just an application used to access SQL Server. Use select @@version if you ever need to know your SQL Server version.

answered Jun 22, 2021 at 7:37

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stilllearning Over a year ago

Stu - you are wonderful - thank you for the notes and explanation! Joins are still a bit confusing to me - I have a loooong way to go :)

Collectives™ on Stack Overflow

How do I insert a subquery with multiple results into multiple rows?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related