Update Table1 From table 2

Question

I have two tables in my database and I want to update Table 1 from Table 2

Table 1 looks like this:

id - eventid - hostname   - trigger - startime      - endtime  
1     00001  - 10.10.10.1 - PROB    - 2017-08-03 18:33  ?
2     00001  - 10.10.10.1 - PROB    - 2017-08-05 00:33  ?
3     00001  - 10.10.10.1 - PROB    - 2017-08-05 00:32  ?
4     00002  - 10.10.10.2 - PROB    - 2017-08-02 14:58  ?
5     00002  - 10.10.10.2 - PROB    - 2017-08-07 17:59  ?
.
.

And Table 2 looks like this:

id - eventid - hostname   - trigger - startime       
1     00001  - 10.10.10.1 - OK    - 2017-08-03 19:22  
2     00001  - 10.10.10.1 - OK    - 2017-08-05 03:13  
3     00001  - 10.10.10.1 - OK    - 2017-08-05 04:30  
4     00002  - 10.10.10.2 - OK    - 2017-08-02 15:08  
5     00002  - 10.10.10.2 - OK    - 2017-08-07 19:29
.
.

How can I update Table 1 so I can insert startime from Table 2 into endtime on Table 1?

score 0 · Accepted Answer · answered Aug 28 '17 at 08:38

0

You can join the two tables in your UPDATE query (read about it in the documantation):

UPDATE `Table 1` t1
    JOIN `Table 2` t2 ON t1.ID = t2.ID
SET t1.endtime = t2.starttime

answered Aug 28 '17 at 08:38

waka

3,362
9
35
54

Actually the id of the two tables are different only the eventid is the same and i used this command but it keeps insert only the first match form table2 for all the rest i mean like this id - eventid - hostname - trigger - startime - endtime 1 00001 - 10.10.10.1 - PROB - 2017-08-03 18:33 2017-08-03 19:22 2 00001 - 10.10.10.1 - PROB - 2017-08-05 00:33 2017-08-03 19:22 3 00001 - 10.10.10.1 - PROB - 2017-08-05 00:32 2017-08-03 19:22 – wael Aug 28 '17 at 08:43
@wael: If the IDs are different, then how can you be sure which endtime should be inserted in which row in table 1? – waka Aug 28 '17 at 08:44

score -1 · Answer 2 · edited Aug 28 '17 at 13:32

Same query. different join syntax.

The first is old style ANSI, the latter is later ANSI. If you must pick between them, pick the latter.

I wouldn't use either of them due to the limitations of USING (can't be aliased). Instead write as:

UPDATE table1 A 
INNER JOIN (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) as B
  ON B.id = A.id 
SET A.Freq = B.idcount
See this Oracle answer as to why USING is not to be preferred: https://stackoverflow.com/questions/456684/mixing-using-and-on-in-oracle-ansi-join

And here is a similar discussion for SQL Server:

In general, get used to writing SQL syntax in a way that is portable. While you may never port your code, you will at least be able to port your skills around to multiple database (Which is generally a cool thing to do to avoid becoming a one-trick pony).

Update Table1 From table 2

2 Answers2

Linked