CODEWITHSUNDEEP
sqlsql-servert-sqlsql-insert
Stilllearning
Stilllearning

Reputation: 3

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

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.

Upvotes: 0

Views: 60

Answers (1)

Stu
Stu

Reputation: 32579

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.

Upvotes: 2

Related Questions