SQL question

[Pallex]

Say you have the following 2 tables:

table1: books
col1: unique_id
col2: title
col3: author
col4: sales

table2: sales
col1: unique_id
col2: author
col3: total sales:

here's some sample data:

books:
1,book1,mr smith, 100
2,book2,mrs jones, 20

sales:
1,mr smith, NULL
2,mrs jones, 19

i want to put the `sales` value from the first record in books into the `total tales` column in the first record in sales.

I just can't get it to happen. I've tried a number of approaches. I'm using Sql Server 2000.

[Ecniv]

table1: books
col1: unique_id
col2: title
col3: author
col4: sales

table2: sales
col1: unique_id
col2: author
col3: total sales:

here's some sample data:

books:
1,book1,mr smith, 100
2,book2,mrs jones, 20

sales:
1,mr smith, NULL
2,mrs jones, 19

Try the following - you'll have to find the equivalent field types in Sql server - I'll be using Access ones:

tblBooks
BookID - autonumber - PK
Title - text - 100
AuthorID - number (Long)

tblAuthors
AuthorID - auto - PK
Forename - text - 50
Surname - text - 50

tblSales
SaleID - auto - PK
DateTime - Date/Time
CashierID - number (long)

tblSalesDetails
SalesDID - auto - pk
SalesID - number (long) - fk
BookID - number (long) - fk
QtySold - number (byte)

Select tbBooks.title, tblAuthors.Surname, tblAuthors.Forename, qrySDets.TotalSales

From 
tblBooks Left join
(Select tblSalesDetails.BookID, sum(tblSalesDetails.QtySold) as TotalSales from tblSalesDetails) as qrySDets on tblBooks.BookID=qrySDets.BookID
Left join tblAuthors on tblBooks.AuthorID=tblAuthors.AuthorID
Order By tblAuthors.surname, tblAuthors,forename

This was written off the top of my head so may not work straight away, but gives you an idea....?

Vince

[Pallex]

Thanks, but i don't want to return anything - i want to edit a column in a row. I think I need Update.

Your code may return the correct values, but what i need is something like:

update sales
set total_sales = 2
where sales.unique_id = 1

except where i have '2' in my example i want the result of:

select sales from books
where books.unique_id = 1

only when i try combining the 2 i get an error which i've forgotten now but which implied i can't do it because the sql parser thinks i'm using an aggregate. presumably this is because the select statement could return more than one value, where the update only wants one.

i've tried - and seen examples - of people doing this sort of thing, only i just can't get it to work. Perhaps the examples were for oracle or postgres or what have you.

Any ideas?

thanks,
alex.

[Ecniv]

Originally posted by Pallex
Thanks, but i don't want to return anything - i want to edit a column in a row. I think I need Update.

Your code may return the correct values, but what i need is something like:

update sales
set total_sales = 2
where sales.unique_id = 1

except where i have '2' in my example i want the result of:

select sales from books
where books.unique_id = 1

only when i try combining the 2 i get an error which i've forgotten now but which implied i can't do it because the sql parser thinks i'm using an aggregate. presumably this is because the select statement could return more than one value, where the update only wants one.

i've tried - and seen examples - of people doing this sort of thing, only i just can't get it to work. Perhaps the examples were for oracle or postgres or what have you.

Any ideas?

thanks,
alex.

Update
sales left join
(select books.sales from books where sales.unique_id=1) as qryBooks on sales.unique_id=qryBooks.unique_id
Set sales.total_sales=books

Don't know if that'll work - worth a try?


Vince

[Pallex]

Vince wrote:

Update
sales left join
(select books.sales from books where sales.unique_id=1) as qryBooks on sales.unique_id=qryBooks.unique_id
Set sales.total_sales=books

Don't know if that'll work - worth a try?

-----------

I don't think you can do that with Update -Is that valid syntax? Not according to my reading of my O'Reilly SQL book.

[si_the_geek]

The syntax of that kind of update depends on which version of SQL is on the DBMS - each seems to have its own variation. (what Ecniv wrote does look familiar, but isn't right for SQL Server).

I think for SQL Server it needs to be like this (assuming there is only one matching row in Books):

UPDATE sales 
SET total_sales = books.sales
FROM books 
WHERE sales.unique_id=Books.unique_id

or with a sub-query (to get a total value of mulitple rows):

UPDATE sales 
SET total_sales = (SELECT Sum(books.sales) FROM books 
                   WHERE sales.unique_id=Books.unique_id)