0

So, I am a classically trained cabinet maker, there fore, I am a mysql hack. I have been hacking scripts for a couple of years now, but this one is stumping me. Please help. I am trying to write a trigger, update a row when a criteria is meet.

This one throws no errors, but no update

INSERT INTOstuff(orders_id,date_purchased,customers_company,order_total,customers_name)
SELECT zenoo_orders, orders_id,date_purchased,customers_company,order_total,customers_name FROM zenoo_orders
WHERE zenoo_orders.customers_id='stuff.customers_id'

this one errors

INSERT INTOstuff(orders_id,date_purchased,customers_company,order_total,customers_name)VALUES(NEW.orders_id,NEW.date_purchased,NEW.customers_company,NEW.order_total,NEW.customers_name)
SELECT zenoo_orders, orders_id,date_purchased,customers_company,order_total,customers_name FROM zenoo_orders
WHERE zenoo_orders.customers_id='stuff.customers_id'

This one writes a new row, but I need to update an existing

INSERT INTOstuff(orders_id,date_purchased,customers_company,order_total,customers_name)
VALUES(NEW.orders_id,NEW.date_purchased,NEW.customers_company,NEW.order_total,NEW.customers_name)

Any help would be much appriciated.

GarethD
  • 68,045
  • 10
  • 83
  • 123
rpc
  • 1
  • *"but i need to update an existing"* - Are you just looking for the `UPDATE` statement instead of the `INSERT` statement? – David May 03 '22 at 19:24
  • *This one throws no errors, but no update* 1) INSERT won't update; 2) A space before tablename is lost. *this one errors* ``INSERT .. VALUES .. SELECT ..`` not exists. *this one writes a new row, but i need to update an existing* INSERT inserts, UPDATE updates... – Akina May 03 '22 at 19:28
  • Does this answer your question? [MySQL syntax for Join Update](https://stackoverflow.com/questions/2114534/mysql-syntax-for-join-update) –  May 03 '22 at 19:42
  • David, an update would be great. I tried this but mysql would not accept it: UPDATE AFTER UPDATE stuff(orders_id,date_purchased,customers_company,order_total,customers_name) SELECT zenoo_orders, orders_id,date_purchased,customers_company,order_total,customers_name FROM zenoo_orders WHERE zenoo_orders.customers_id='stuff.customers_id' – rpc May 04 '22 at 11:21
  • Thanks Kendle, but join update will not solve my problem. I need to update based on a where – rpc May 04 '22 at 16:38
  • table "stuff" is getting updated by table "zenoo_orders" but I need it to update only if "customers_id" match in both table – rpc May 05 '22 at 12:21
  • Still stuggling, I can get all the fields to write to the base correctly, but not when the condition is included "WHERE zenoo_orders.customers_id='stuff.customers_id' " If someone would point me in the right direction, I would be very greatful – rpc May 06 '22 at 15:32
  • If you have a problem coping crown or maybe cutting dovetails, I can help. – rpc May 13 '22 at 18:55

0 Answers0