Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    41

    Unanswered: Updating the table without knowing the value

    Please consider the following table schema which I am referring to for writing queries:

    The following are the tables that I am using:

    Table #1: Customer ; Table #2 : Product ; Table #3: Order


    Code:
    Customer    
    customer_id
    first_name
    last_name
    address_1
    address_2
    city
    state
    country
    zip
    discount
    active
    Code:
    Product
    product_id
    product_name
    desc
    manufacturer
    quantity
    sku_number
    unit_price
    vendor
    Code:
    Order
    order_id
    date
    customer_id
    product_id
    billing_name
    billing_addr1
    billing_addr2
    billing_city
    billing_state
    billing_country
    billing_zip
    quantity_sold
    total_price
    I would like to update the columns billing_name, billing_addr1, billing_addr2, billing_city, billing_state, billing_country, billing_zip in the ORDER table based on active customers in the CUSTOMER TABLE.

    I don't have any other information available like which database it is. So, if I need to come up with the query for above question, I am wondering to what values I should update since I just have table structure with me? Also, how would I know which customers are active or not?

    I could think of something to start as :

    UPDATE Order ord
    SET ord.billing_name = 'value1' , ord.billing_addr1 = 'value2' & so on

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what value do you not know?

    When you create the order, Im guessing you offer the customer the option of either using the existing address from the customers table or whatever delivery address they have specified as part of the order capture process.

    but if you must do it another way, you could use an update query, seems daft to do it this way but

    Code:
    update orders set invoice_addr1 = customers.address_1, invoice_addr2 = customers.address_2
    join customers on orders.customer_id = orders.customer_id
    where isnull(invoice_addr1)
    as to which customers are active, well define active....
    presumably an active customer is one wo has purchased something in the last n time periods
    ...so by using the suitable MySQL datetime function you can find what customers have place and order within the required n time periods by doing a JOIN between the customers and orders table

    As a suggestion I'd recommend that you pay attention to your naming of tables and columns. There are nearly as many naming conventions as promises made by a politician in the rn up to an election, wahtg ever convention you do decide to use stick with it. So if you want to abbreviate address to addr, do so, but dont' use addr and address inside the same schema
    Either use CamelCase or lower_case_with_underscore but don't mix

    there's recommendations that you don't repeat the table name in column names within the same table. so instead of using customer_id inside the customers table use is instead (however do use the table name as a prefix when its a foreign key, it makes it easy to track what column is a foreign key pointing to what table. so customer_id in the orders table is all well and good.

    Does your design really only allow a single product per customer order?

    there is something odd about your orders table. usually I'd expect customer ordsxers to be modelled as (at least) 2 tables. one for the header information (eg order date, who placed it, delivery addres etc) and one comprising the the items ordered. in the order detail I/d expect there to be (at the very least) the product, the qty ordered the price.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •