Insert into Table2 from Table 1

Question

I have table1 which contain all data log :

id    hostname     trigger     date
1    10.10.10.1   PROBLEM    2017-08-04 14:10
2    10.10.10.1   OK         2017-08-04 14:56
3    10.10.10.1   PROBLEM    2017-08-04 15:42
4    10.10.10.1   OK         2017-08-04 15:43
5    10.10.10.2   PROBLEM    2017-08-09 01:19
6    10.10.10.2   OK         2017-08-09 01:58
.
.

i need to show this data in different form so i can print it in web interface How can i insert Into Table 2 data from table 1 so it will look like this :

id    hostname     triprob    datpro          triok            dateok
1    10.10.10.1    PROBLEM  2017-08-04 14:10   OK            2017-08-04 14:56
2    10.10.10.1    PROBLEM  2017-08-04 15:42   OK            2017-08-04 15:43
3    10.10.10.2    PROBLEM  2017-08-09 01:19   OK            2017-08-09 01:58
.

Thank you .

You have shown no effort here, but what's more is that your problem is rife with unforeseen edge cases. Explain the logic for the grouping in your output. — Tim Biegeleisen, Aug 28 '17 at 10:21
This seems to be a repost of https://stackoverflow.com/q/45914837/1415724 and it should probably be closed as an exact duplicate, as it received an answer. — Funk Forty Niner, Aug 28 '17 at 10:22
no it's not the same here i have all data in one table ...i'am new in mysql query please help — wael, Aug 28 '17 at 10:28
Possible duplicate of [Update Table1 From table 2](https://stackoverflow.com/questions/45914837/update-table1-from-table-2) — Priyal, Aug 28 '17 at 10:36
my data table is different here i need rebuild it in other form — wael, Aug 28 '17 at 10:45
Another possible issue is you have no direct confirmation of which OK goes with which problem. is it ALWAYS a 1:1? Can there be multiple Problems before the first OK result for an IP? If so, which OK goes with which problem. First ok to first problem then repeat? — DRapp, Aug 28 '17 at 10:54
for the same host can be many problem and many ok ....the table is sorted like problem and datetime then ok and datetime — wael, Aug 28 '17 at 10:59
Leaving aside the complicatons that DRapp hints at (the story could be a LOT more complex) if your only reason for having the results in the seocnd table is to format them as you describe then **you don't need a second table** you just need a query which presents the data in your desire format (which is also a precursor to populating a second table) — symcbean, Aug 28 '17 at 11:21

score 1 · Accepted Answer · answered Aug 28 '17 at 11:26

Ok, so you are new to querying, and not as simple as many queries. Here is a SQLFiddle of your sample

First, because you have no indicator on which OK goes with which problem, it is IMPLIED that they are resolved on a first hit, first resolved for the respective host.

Now, the table column names. If you can, try to AVOID using table names AND column names that are otherwise reserved words, they can get in the way. (Trigger, Date for example).

Now, look at the issue you need to resolve. For each problem (outermost WHERE clause), you need to find the FIRST "OK" that corresponds to that problem / host. So here, I am using the same table 3 times, but using different "alias" name references so I know which one is which (via tProb, tOk, tChk ).

The left-join between the problem and the Ok is because not all problems may be resolved. This will allow the problem to be shown as OPEN if no corresponding Ok record found. The join condition itself is rather simple... Joining on the same host name.

However, the second part of the join for the ID is the tricky one. Here, it is a CORRELATED SUB-QUERY (which are typically performance killers). For each problem, you need to find the FIRST (via MIN) ID that is for that host, and is an OK status AND the ID is greater than the ID of the problem. You don't want an OK with ID = 2 be associated to PROBLEM with ID = 3. By using the MIN(), you will be guaranteed only a single entry if any.

Finally, the field selection of COALESCE() is to identify get the value from the ok table if one is so found. If NOT found, leave a blank value for display.. or '- still open -' for the Ok Status.

SELECT
      tProb.id as ProblemID,
      tProb.hostName,
      tProb.trigger as Problem,
      tProb.date as ProblemDate,
      coalesce( tOk.id, 0 ) as OkID,
      coalesce( tOk.`trigger`, '- still open -' ) as OkStatus,
      coalesce( tOk.`Date`, ' ' ) as OkDate
   from
      Table1 tProb
         LEFT JOIN Table1 tOk
            ON tProb.hostName = tOk.hostName
            AND tOk.ID in 
                ( select
                        MIN( tChk.ID ) as ID 
                     from
                        Table1 tChk
                     where
                            tChk.HostName = tProb.HostName
                        AND tChk.ID > tProb.ID
                        AND tChk.trigger = 'OK' )
   where
      tProb.trigger = 'PROBLEM'
   order by
      tProb.id

To help simplify this query, you might be best to add another column to the table for problemID. When a solution entry is entered, it should insert the ID of the problem as a basis there. Not necessarily a requirement to backstamp the original problem, just the solution to identify which problem it was resolving from the queue.

Thank you very much @DRapp this is exactly what i want i will test it — wael, Aug 28 '17 at 11:40
@wael, glad to help. Also, click the checkbox next to the answer so others know it is resolved and helps others under similar searches. There used to be a link for site etiquette on posting questions, marking as answered, up/down voting too. In future, when you need, edit your original post with changes/revisions when asked by someone. Then let that person know you modified instead of plugging into a comment like this. — DRapp, Aug 28 '17 at 13:33

score 0 · Answer 2 · edited Aug 28 '17 at 17:03

0

To copy partial data from an existing table to the new one, you use WHERE clause in the SELECT statement as follows:

CREATE TABLE new_table 
SELECT col, col2, col3 
FROM
    existing_table;

edited Aug 28 '17 at 17:03

FelixSFD

6,052
10
43
117

answered Aug 28 '17 at 12:43

Pankaj Yadav

303
2
7

Insert into Table2 from Table 1

2 Answers2