SQL question

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.

Quote:

---

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:

Code:

---

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)

---

Code:

---

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

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.

Quote:

---

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

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.

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):

Code:

---

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):

Code:

---

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

---