1

I'm working with two tables. 

 Table1(Source): nextgenorders2
 Table2(My Table): mytable_nextgenorders2

Table 1 is the source data. I have no control over this and I have to work with what I have.

Table 2 is my table which has the same data as Table 1 with some additional columns.

The problem I have is if yesterday the records for the company name "Deeway HS" has 3 records in the source table 1, but today it has 10 new records in the source table 1, I don't want to have to manually initiate a query to save those new records to the table 2.

I need to insert those new records into Table 2 with the same value for the tech column and the status column as the other matching records that have the same company name automatically.

Is a MYSQL trigger the best solution for the scenario? Or a query that runs on an interval?

Here's a SQL Fiddle:

http://sqlfiddle.com/#!9/d75e30/11

And here are tables

Table 2(my table)

  ID | Company         | Entity |   Order#    |   Date     |    Serial    |    Item   |  Tech |   Status |
  1    Deeway             20779    4608580      2020-11-11       SN1           Item1      JD      Gathered
  2    Deeway Pearl HS    20780    4608580      2020-11-11       SN2           Item2      JD      Gathered
  3    Deeway Stony HS    1273     4608558      2020-11-11       SN3           Item3      JD      Gathered
  4    Zaper LTD          9995     4630230      2020-11-11       SN4           Item4      MJ      Assigned
  5    Zaper CP LTD       4295     4607371      2020-11-11       SN5           Item5      MJ      Assigned
  6    Wilder             4224     6630210      2020-11-11       SN6           Item6      PJ      Assigned
  7    Wilder             4224     7601371      2020-11-11       SN7           Item7      PJ      Assigned

Table 1(Source)

 ID | Company         | Entity |   Order#    |   Date     |    Serial    |    Item
 1    Deeway             20779    4608580      2020-11-11       SN1           Item1
 2    Deeway Pearl HS    20780    4608580      2020-11-11       SN2           Item2
 3    Deeway Stony HS    1273     4608558      2020-11-11       SN3           Item3
 4    Zaper LTD          9995     4630230      2020-11-11       SN4           Item4
 5    Zaper CP LTD       4295     4607371      2020-11-11       SN5           Item5
 6    Wilder             4224     6630210      2020-11-11       SN6           Item6
 7    Wilder             4224     7601371      2020-11-11       SN7           Item7 
 8    Deeway             20779    4608580      2020-11-11       SN8           Item8
 9    Deeway             20779    4608580      2020-11-11       SN9           Item9
 10   Wilder             4224     7601371      2020-11-11       SN10          Item10 
 11   Zaper LTD          9995     4630230      2020-11-11       SN11          Item11

Here is my attempt, it does not add the new records, it only updates the existing records or inserts when there are no records of the company name in Table 2(my table).

$query_checkifexist = mysqli_query($mysqli, "
SELECT * 
FROM mytable_nextgenorders2 
WHERE Company LIKE CONCAT(SUBSTRING_INDEX('$mytable_nextgenorder_companyname', ' ', 1),'%') 
 ");


$row_check          = mysqli_num_rows($query_checkifexist);



if ($row_check > 0) {

$query_update = mysqli_query($mysqli, "
UPDATE mytable_nextgenorders2 SET
mytable_nextgenorder_companyname='$mytable_nextgenorder_companyname',
mytable_nextgenorder_company_entity='$mytable_nextgenorder_company_entity',
mytable_nextgenorder_ordernumber='$Entmytable_nextgenorder_ordernumberity',
mytable_nextgenorder_deliverydate='$mytable_nextgenorder_deliverydate',
mytable_nextgenorder_serialnumber='$mytable_nextgenorder_serialnumber',
mytable_nextgenorder_item='$mytable_nextgenorder_item',
mytable_nextgenorder_tech='$mytable_nextgenorder_tech',
mytable_nextgenorder_tech='$mytable_nextgenorder_status'
WHERE mytable_nextgenorder_companyname LIKE CONCAT(SUBSTRING_INDEX('$mytable_nextgenorder_companyname', ' ', 1),'%') ");


} else {


if (!$mysqli->query("INSERT INTO mytable_nextgenorders2 (
mytable_nextgenorder_companyname,
mytable_nextgenorder_company_entity,
mytable_nextgenorder_ordernumber,
mytable_nextgenorder_deliverydate,
mytable_nextgenorder_serialnumber,
mytable_nextgenorder_item,
mytable_nextgenorder_tech,
mytable_nextgenorder_status
)
SELECT 
GROUP_CONCAT(DISTINCT nextgenorder_companyname) AS nextgenorder_companyname,
GROUP_CONCAT(DISTINCT nextgenorder_company_entity) AS nextgenorder_company_entity, 
GROUP_CONCAT(DISTINCT nextgenorder_ordernumber) AS nextgenorder_ordernumber,
nextgenorder_deliverydate,
nextgenorder_serialnumber,
nextgenorder_item,
'$mytable_nextgenorder_tech',
'$mytable_nextgenorder_status'
FROM nextgenorders2
WHERE Company LIKE CONCAT(SUBSTRING_INDEX('$nextgenorder_companyname', ' ', 1),'%') ")) }
Kyocera Alerts
  • 125
  • 7
  • Use `INSERT INTO ... ON DUPLICATE KEY UPDATE ...` – Barmar Mar 10 '20 at 19:12
  • What about the tech and status column? I need to automatically use the same value for the tech and status column as the other matching rows that have the same company name on insert. – Kyocera Alerts Mar 10 '20 at 19:13
  • Use a self-join to get those. – Barmar Mar 10 '20 at 19:17
  • Can you give an example? I've never worked with self-join before. Thank you so much for your time. – Kyocera Alerts Mar 10 '20 at 19:20
  • When `$mytable_nextgenorder_companyname` is `Deeway` you want to update `Deeway`, `Deeway Pearl HS` and `Deeway Stony HS` with the same values? – Barmar Mar 10 '20 at 19:23
  • Is there a unique index on the destination table other than `ID`? – Barmar Mar 10 '20 at 19:30
  • Can you show what the desired result should be when updating the table from that source? – Barmar Mar 10 '20 at 19:30
  • Why are you using `GROUP_CONCAT()` in your `INSERT`? That will combine all the `Deeway XXX` rows into a single row. – Barmar Mar 10 '20 at 19:32
  • There's no unique ID, the order number is always different, company name can have an abbreviation, and company entity can sometimes be different. I'm using the company name with group_concat and LIKE wildcards – Kyocera Alerts Mar 10 '20 at 19:33
  • When $mytable_nextgenorder_companyname is LIKE Deeway/Deeway Pearl HS/Deeway Stony HS and table 2 has new records that are not in table 1, insert into table 1, make tech column and status column the same as existing Deeway/Deeway Pearl HS/Deeway Stony HS records in table 1 – Kyocera Alerts Mar 10 '20 at 19:33
  • **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Mar 10 '20 at 19:33
  • @KyoceraAlerts Are those columns guaranteed to be the same in all rows with the same company name prefix? – Barmar Mar 10 '20 at 19:35
  • I'm having trouble understanding what this is supposed to do. The source table has 5 Deeway rows, the destination has 3. Is it supposed to update the 3 rows, add 5 new rows, or both update and insert? In the latter case, how does it know which rows to update versus insert? – Barmar Mar 10 '20 at 19:39
  • Yes, those columns are guaranteed to be the same in all rows with the same company name. The source has 5 Deeway rows and the destination has 3. I need it to insert the new rows that dont exist on the destination and update the tech and status value on the new rows because on insert those fields will be blank. – Kyocera Alerts Mar 10 '20 at 20:29

0 Answers0