SQL statement construction help

[fireantz]

hi there

need help with sql query construction...

Scenario
Customer has access to update item price list (item_price), however my application does not read directly from this table because i do not want the price to change real time. I have a separate table (price_main) to keep the price list. For price sync up, i'll be running a sql script to update everyday at a specific time. I need help with the construction here.

Table structure
item_price
item_id (PK)
item_price

price_main
item_id (PK)
price
updated_on


Requirement
- to sync price_main from item_price table.
- if the price is the same, it is not necessary to update the price_main table.

 

[saysuzu]

er...which part of the SQL query construction you need help on?

 

[fireantz]

i did something like this, however it seems to have syntax error


update PM set
PM.price= IP.item_price
FROM item_price IP, price_main PM
where
PM.item_id = IP.item_id
PM.price <> IP.item_price

 

[davidktw]

hi there

need help with sql query construction...

Scenario
Customer has access to update item price list (item_price), however my application does not read directly from this table because i do not want the price to change real time. I have a separate table (price_main) to keep the price list. For price sync up, i'll be running a sql script to update everyday at a specific time. I need help with the construction here.

Table structure
item_price
item_id (PK)
item_price

price_main
item_id (PK)
price
updated_on


Requirement
- to sync price_main from item_price table.
- if the price is the same, it is not necessary to update the price_main table.

Assuming you are using MySQL. Write a simple script to invoke your mysql utility. Run the following sql codes

CREATE TABLE price_main_new SELECT * FROM item_price;
RENAME TABLE price_main TO price_main_backup, price_main_new TO price_main;
DROP TABLE price_main_backup;

I don't see the need for your "updated_on" column to exist if it is the same value across all rows. Unless I'm mistaken, I assume the reason for the column is to indicate when this Materialise View is created ?

 

[saysuzu]

i did something like this, however it seems to have syntax error


update PM set
PM.price= IP.item_price
FROM item_price IP, price_main PM
where
PM.item_id = IP.item_id
PM.price <> IP.item_price

update statement not like that executed one

update structure is like this:
update [tablename]
set [col1] = [value],
col2 = [value]
where col1 = [value]


your [value] can be another select subquery statement