Update column with calculation and DATEDIFF

Question

I have a Transaction table and a Car table.

tID	cID	carID	eID	tDate	PickupDate	ReturnDate	Amount_Due
1002	1006	1004	104	2018-04-18	2018-04-28	2018-04-2	NULL

Car

CarID	Make	Model	Type	Year	Price
1004	Ford	Focus	Hatch	2019	140.00

I need to update the Amount due column with Price * DATEDIFF(day, [Transaction].PickupDate, [Transaction].ReturnDate)

I know I need an inner join but not sure how to write the query correctly. This is what i have so far but I think it's way off. I'm very new to SQL

UPDATE [Transaction]
SET Amount_Due = INNER JOIN CAR ON [Transaction].carID=Car.carIS * (DATEDIFF(day, [Transaction].PickupDate, [Transaction].ReturnDate))

Have a look at this [example](https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15#l-specifying-a-table-alias-as-the-target-object) in the documentation; you'll see where you're going wrong. — Thom A, Apr 16 '21 at 11:43
Does this answer your question? [How can I do an UPDATE statement with JOIN in SQL Server?](https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server) — Charlieface, Apr 16 '21 at 12:19

score 0 · Accepted Answer · answered Apr 16 '21 at 11:42

0

The syntax in SQL Server for a JOIN in an UPDATE is:

UPDATE t
    SET Amount_Due = c.Price * DATEDIFF(day, t.PickupDate, t.ReturnDate)
        FROM [Transacction] t INNER JOIN
             CAR c
             ON t.carID = c.carID ;

answered Apr 16 '21 at 11:42

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon that worked, really appreciate the help. I'm not very familiar with aliases and what to clarify something. When you've put 't' after [Transaction] on line 3, same with car on line 4, is that creating the alias for the tables? Apologies if this is a stupid question. – Joseph Redding Apr 16 '21 at 12:06
@JosephRedding . . . Such questions when you are learning are not "stupid", just "basic". In the above query, `t` and `c` are table aliases. You can think of them as convenient abbreviations for the table references, although they are needed in come cases (such as when the `FROM` clause uses the same table multiple times). – Gordon Linoff Apr 16 '21 at 12:41

Update column with calculation and DATEDIFF

1 Answers1