Inserting other column data into new row

Question

I have a database with columns id, begin, end, route. Is there a way to select the max or previous rows end column value and insert it as the new row's begin value?

ID  Begin  End   Route
1     1    10     SW
2    10    21     NE
3    21    55     S

What I am trying to do is this...

INSERT INTO log ('id', 'begin', 'end', 'route') 
VALUES (DEFAULT, MAX('End"), 'xxx', 'xxx')

There is a way. But without knowing the layout of your data, it is really hard to say more. — Gordon Linoff, Dec 01 '14 at 19:52

aphextwix · Answer 1 · 2014-12-01T20:21:53.837

0

You'll need the MAX function to select the maximum value.

SELECT MAX(*column name*) AS *alias* FROM *table name*;

sgeddes and Slowcoder have provided answers for the INSERT statement.

edited Dec 01 '14 at 20:21

answered Dec 01 '14 at 19:49

aphextwix

1,838
3
21
27

score 0 · Answer 2 · answered Dec 01 '14 at 20:13

0

Given your edits, something like this should work using max with insert into select:

INSERT INTO log (`begin`, `end`, `route`) 
SELECT MAX(`End`), 'xxx', 'xxx'
FROM log

Condensed Fiddle Demo

Please also notice the use of backticks vs single quotes. Use the backticks to delimit fields.

answered Dec 01 '14 at 20:13

sgeddes

62,311
6
61
83

I get the following error. "#1248 - Every derived table must have its own alias" – the1Texan Dec 01 '14 at 20:42
@the1Texan -- this query doesn't use any derived tables (subqueries). That error generally means you to alias your subquery. What's your query? – sgeddes Dec 01 '14 at 20:45
INSERT INTO `log`(`ID`, `begin`, `end`, `route`) VALUES (DEFAULT,(SELECT maxend FROM (SELECT MAX(`end`) FROM `log`),'5000.1','SW') – the1Texan Dec 01 '14 at 21:28

score 0 · Answer 3 · answered Dec 01 '14 at 20:14

0

Try something like this.

INSERT INTO log (`id`, `begin`, `end`, `route`) VALUES (DEFAULT, (SELECT MAX(end) from log), 'xxx', "xxx');

answered Dec 01 '14 at 20:14

Slowcoder

2,060
3
16
21

This isn't valid sql syntax -- just because the OP writes it, doesn't mean we should copy it! – sgeddes Dec 01 '14 at 20:15
Why isn't it valid? Just curious. – aphextwix Dec 01 '14 at 20:17
@aphextwix -- before the edits, single quotes instead of backticks -- after the edits, double quote still needs to be removed --> `"xxx'` won't work either. Getting closer though... – sgeddes Dec 01 '14 at 20:18
@sgeddes - Oh but the actual syntax of using a sub-query for the `SELECT` is fine though? – aphextwix Dec 01 '14 at 20:20
1

@aphextwix -- well, that too won't work either without tricking mysql with a subquery. Here's an example: http://sqlfiddle.com/#!2/d0b00b/1 – sgeddes Dec 01 '14 at 20:24
@sgeddes is right. The select query won't work here without a different table alias. http://stackoverflow.com/questions/8333376/you-cant-specify-target-table-table-name-for-update-in-from-clause – Slowcoder Dec 01 '14 at 21:43

Inserting other column data into new row

3 Answers3