Update a table record using another table data in a database?

Table one: products Which have fields like products_id products_price master_categories_id

Table two:specials. This table is empty,Fields in it are

specials_id(an autoincrement field)

products_id(the value same as table one)

 specials_new_products_price (the value equals products_price-(products_price*0.05))

specials_date_added (the value like this 2011-12-05 12:17:44)

 specials_last_modified (the value like this  2011-12-05 12:19:10)

 expires_date  (2011-12-31)

date_status_change  (the value like this 2011-12-05 12:19:10)

status  (1) 

specials_date_available  (2011-11-29)

now, i want to insert the data (“SELECT products_id,products_price FROM products where master_categories_id=79”) into table 2. and set the

status=1, specials_date_available=2011-11-29, expires_date=2011-12-31. products_id(the value same as table one) [B]specials_new_products_price (the value equals products_price-(products_price*0.05)[/B]

Use INSERT INTO tablename SELECT … FROM … WHERE …

You can put fixed values along with column names in the SELECT part (just like a normal SELECT query).

INSERT INTO specials( specials_new_products_price,products_id,specials_date_available,status,expires_date) SELECT products_id,products_price FROM products where master_categories_id=79

i run the above sql command .it doesn’t work

1,how to set the rest field’s value?

2,how to set the specials_new_products_price?

Like I said… :smiley:


INSERT INTO specials (specials_new_products_price, products_id, specials_date_available, status, expires_date)
SELECT 
    products_price - (products_price*0.05)
  , products_id
  , '2011-11-29'
  , '1'
  , '2011-12-31'
FROM products 
WHERE master_categories_id = 79