Lets say a have 2 tables and both have a datetime column.
On table A, on datetime column, lets say, ts1, is non null, default is CURRENT_TIMESTAMP and on update CURRENT_TIMESTAMP;
The other table, B, the datetime column, lets say, ts2 is non null;
The datetime values on both tables must be the same (A.ts1==B.ts2).
As ts1 is auto-updated on inserts and updates, I don't need to get the current_stamp on advance.
Now, how can I do inserts on table B in such way that B.ts2 has the same timestamp value (A.ts1) from a specific record on table A?
Is there a relation ship between the tables to achieve this or some special insert/select statement ?
sorry if the answer is obvious...I am a new to databases... PS: Using mySql 5.7 innoDB.
PS2: I found this works, but seems to be awkward.
insert into B (y, ts2) values ('a value for y', (select ts1 from A where x=1));
any other better ideas ?
@Lennart: Thanks for your comments. Yes I need help in the design. I am open to suggestions.
Developing this a bit more:
On table A, x is a key. Also, ts1 can not be unique since more than 1 record can have the same ts, because of possible concurrent access to create records on A.
Currently, there is no keys on table B. The column ts2 can not be unique too, since table B store several y values with the same timestamp ts2; In fact the excepted usage of table B is something like:
select * from B where ts2=(select ts1 from A where x=somevalue);
I liked how this sounds "add a trigger that does the update". Can you develop this a bit more, please ?
PS3: @Lennart, here we go:
CREATE TABLE `device` ( `deviceID` varchar(12) NOT NULL, `snmpSet` varchar(30) NOT NULL, `firstSeen` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `lastSeen` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf16; ALTER TABLE `device` ADD PRIMARY KEY (`deviceID`); COMMIT; CREATE TABLE `snmpData` ( `deviceID` varchar(12) NOT NULL, `ts` datetime NOT NULL, `oid` varchar(45) NOT NULL, `value` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf16 COMMENT='hold all snmp data from each device';
if it is a new device, insert it into table device and add its data to snmpData
INSERT INTO device (deviceID, snmpSet) VALUES ($id, $snmpSet); INSERT INTO snmpData (deviceID, ts, oid, value) VALUES ($id, (SELECT lastSeen FROM device WHERE deviceID=$id),$oid,$value);
else, it is a existing device, I need to update the lastSeen on device and then, the same insert on snmpData
UPDATE device SET lastSeen=current_timestamp() WHERE deviceID=$id; INSERT INTO snmpData (deviceID, ts, oid, value) VALUES ($id, (SELECT lastSeen FROM device WHERE deviceID=$id),$oid,$value);
The general idea: This DB will support a monitoring tool that store snmp data (oid/value pairs) for several devices (deviceID) The table device holds basic info about every device and the snmpData holds the data itself for every device. If I want to check the last values received from a device I plan to do something like this:
select oid, value from snmpData where deviceID=$id and ts=(select lastSeen from device where device=$id);
as result, I will receive a list of 50 or more records, each one for a specific metric and its value. There is some simplification here. In the real case, both tables have more columns, but you got the idea.
caveats: The code for writing to tables device and snmpData are not on the same logical block. It is a PHP program and the operation on table device has its own try/catch block. If everything it is ok with the INSERT/UPDATE on device table, there is another try/block with the INSERT operation on table snmpData. Each block has its own prepare/execute staements, so it is not possible to use the variable trick "SET @ts := NOW();" that @Rick James suggested. BTW, I loved, its new to me, and I will use it on another context. thanks)