I have two simple tables, parent_table and child_table, but I have spend days to search a right syntax query, I could not find a way to make one query statement for following two simple requirements:
create table if not exists parent_table (id int no null primary key, lastContactTimestamp bigint);
create table if not exists child_table (id int, timestamp bigint, data json, foreign key(id) references(parent_table(id) on delete cascade on update cascade);
(1) Requirement: update child_table, also update parent_table lastContactTimestamp:
if (update child_table) update parent_table set lastContactTimestamp = timestamp_now;
(2) Requirement: Insert a new raw in child_table if timestamp < a, otherwise update child_cable:
update child_table set timestamp = timestamp_now, data = data_value where timestamp > timestamp_now if @@rowcount = 0 insert into table (id, timestamp, data) VALUES (id_value, timestamp_value, data);
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if @@rowcount = 0 insert
if I just run update child_table set timestamp = timestamp_now, data = data_value where timestamp > timestamp_now Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0
How can I get Rows matched output? Does if @@rowcount = 0 a legal syntax in mysql?
