Need help with a query 1

[EdLentz]

I have two tables the first I need to get the productid, and the unitprice. I need to insert into a second table the productid , the unit price * 1.25 into a second table. I also, need to insert into the second table (same record) a pricebookid number that I will enter manually.

this is what I have tried:

insert pricebookproductrel a, products b set a.pricebookid = 1721 and b.productid = a.pricebookid and a.listprice = b.unitpricebookproductrel_price * 1.25
It fails and I am not sure why.

Can someone give me a hand?

Thanks!

 

[r937]

insert 
  into pricebookproductrel
     ( pricebookid
     , listprice )
select productid
     , unitpricebookproductrel_price * 1.25
  from products     
 where productid = 1721

r937.com | rudy.ca

 

[EdLentz]

Thanks for the speedy reply. I think I may not have expressed what I needed quite right. Here are my tables:

pricebookproductrel*/
-------------------------------------------------------------

Field Type
--------- -------------
pricebookid int(19)
productid int(19)
listprice decimal(11,2)

products*/
--------------------------------------------------

Field Type
------------------- -------------
productid int(11)
productname varchar(50)
productcode varchar(40)
productcategory varchar(40)
manufacturer varchar(40)
product_description longtext
qty_per_unit decimal(11,2)
unit_price decimal(11,2)

I need to take each record in products and insert the productid and the unit price multiplied by (a number that I will manually insert) into the productpricebookrel table by productid and the list price will be the product of the unit price multiplication.

So to if a productid is 1793 and the unit price is 100.00 and I want to muntiply it by 1.25 and the pricebook is 1720.
the entry after the query would be:
pricebookrel table
productid: 1793
pricebook: 1720
listprice: 125.00

Thanks for any input.

 

[r937]

insert 
  into pricebookproductrel
     ( pricebookid
     , productid
     , listprice )
select 1720
     , productid
     , unit_price * 1.25
  from products

r937.com | rudy.ca

 

[EdLentz]

That looks like it does the trick!

Thanks you very much! You have saved me alot of time!