Update from Multiple tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update from Multiple tables

Author

Topic

Rhino9
Starting Member

3 Posts

Posted - 2006-05-07 : 12:10:30

Given the below table relationships, I am trying to update the price of disk by %20 percent that were interpreted by 'Joe Smith'. I took a crack at this, but I do not feel comfortable with it. Do I need to do some type of join or union?

UPDATE Disk
SET price = price *.20
WHERE Interpreter.name = ‘Joe Smith;

MusicalWork (idwork, title)
Piece (idpiece, duration, iddisk, idwork)
Disk (iddisk, brand, type, issuingdate, price)
Execute (idpiece, idinterpreter)
Interpreter (idinterpreter, name, address)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-07 : 12:19:57

something like
UPDATE Disk
SET price = price *.20
from Disk d
join Piece p
on p.iddisk = d.iddisk
join Execute e
on e.idpiece = p.idpiece
join Interpreter i
on i.idinterpreter = e.
WHERE i.name = 'Joe Smith'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Rhino9
Starting Member

3 Posts

Posted - 2006-05-07 : 12:28:27

While we are on the subject

I am also stuck on writing a Function for the same table relationship.

I am attempting to write a function to Check the price of every disk and return the number of titles ont he disk which has the highest price. Again - I do not even think that I am in the ballpark:

CREATE FUNCTION highest_price ( )

BEGIN

SELECT count(*) AS
FROM Disk
WHERE price = (SELECT max(price) FROM Disk);

END
/

Thanks in advance!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-07 : 12:42:13

Not sure what you want here

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources