How can I write an SQL statement for this situation?
2 Message(s) by 2 Author(s) originally posted in php sql
| From: laredotornado |
Date: Tuesday, August 07, 2007
|
Hi,
I'm running MySQL 5.0 lon Linux. I have two
table s ...PRODUCTS
-----------------
ID INTEGER NOT NULL
PRICE FLOAT UNSIGNED NOT NULL
PRIMARY KEY (ID)CUSTOM_PRICES
---------------------------
ID INTEGER NOT NULL
PRODUCT_ID INTEGER NOT NULL
PRICE FLOAT UNSIGNED NOT NULL
PRIMARY KEY (ID)
FOREIGN KEY PRODUCT_ID REFERENCES PRODUCTS(ID)The table "CUSTOM_PRICES" will have a subset of
product s from the
products table. What I want to do is update the price in the PRODUCTS
table with the corresponding entry from the CUSTOM_PRICES table,
assuming there is one. If there isn't a corresponding entry in the
CUSTOM_PRICES table, I'd like the price value in the PRODUCTS
table to remain unchanged.How can I do this in a single UPDATE statement?Thanks, - Dave
| From: ZeldorBlat |
Date: Tuesday, August 07, 2007
|
On Aug 7, 11:38 am, "laredotorn...@xxxxxxxxxxx"
wrote in message:
Hi,
I'm running MySQL 5.0 lon Linux. I have two tables ...
PRODUCTS
-----------------
ID INTEGER NOT NULL
PRICE FLOAT UNSIGNED NOT NULL
PRIMARY KEY (ID)
CUSTOM_PRICES
---------------------------
ID INTEGER NOT NULL
PRODUCT_ID INTEGER NOT NULL
PRICE FLOAT UNSIGNED NOT NULL
PRIMARY KEY (ID)
FOREIGN KEY PRODUCT_ID REFERENCES PRODUCTS(ID)
The table "CUSTOM_PRICES" will have a subset of products from the
products table. What I want to do is update the price in the PRODUCTS
table with the corresponding entry from the CUSTOM_PRICES table,
assuming there is one. If there isn't a corresponding entry in the
CUSTOM_PRICES table, I'd like the price value in the PRODUCTS
table to remain unchanged.
How can I do this in a single UPDATE statement?
Thanks, - Dave
update products
set price = c.price
from products p
join custom_prices c
on p.id = c.product_id
Of course that assumes there's only ever one price for any product in
your custom_prices table. According to your create table statement
that is not enforced.
Blogs related to How can I write an SQL statement for this situation?
Address Self Referential Entities in MySQL
This
can be used in two ways. We build a lop and iterate for the select clause. We use the array size (in
php the count($arrayname) command does this for us) and make one line for each number from 1 to size. The fake table names will be
...
Some thoughts on WordPress security
Some of it isn’t too bad, but the admin section in particular is a right unholy mess, with HTML,
PHP code and
SQL statements bundled together haphazardly in a monstrous plate of gone wrong spaghetti bolognese on the loo.
...
SQL Formatter
Our DBA writes some mean
SQL code, I
can write some
SQL that works . It’s not my strongest suit by certainly not my weakest so I handle most of my
sql statements myself. However, when I need his help I cringe at sending him my
sql code
...
Site Index (updated)
db4objects http://www.db4o.com/ (db4o) is a GPLlicensed native object database. You
can store complex objects without using
SQL syntax. There is a db4o example http://svn.boo.codehaus.org/boo/trunk/examples/db4o/ included with boo.
...
100 Open Source Downloads- Part 1
Written in
PHP, this utility handles the administration of MySQL over the Web. phpMyAdmin performs many database administration tasks like running
SQL statements, adding and dropping databases, and adding, editing or deleting tables or
...
Learning Linux Commands150Sorting text filesYou can sort ...
The gat the end of the
statement is a psqlcommand. The attributes of thetable appear in the lines enclosed in parentheses. If a table contains fixed data, you
can also include other
SQL statements(such as INSERT INTO) to load the data
...