2,187
edits
Difference between revisions of "MySQL"
Jump to navigation
Jump to search
→INSERT / UPDATE Rows: Updated sub-headings and added INSERT ... SELECT ... ON DUPLICATE
m (Added Applications category) |
(→INSERT / UPDATE Rows: Updated sub-headings and added INSERT ... SELECT ... ON DUPLICATE) |
||
| Line 171: | Line 171: | ||
== INSERT / UPDATE Rows == | == INSERT / UPDATE Rows == | ||
=== Basic Examples === | |||
<source lang="mysql"> | <source lang="mysql"> | ||
INSERT INTO hosts (name, ping_ok) VALUES ('ServerA', 1) ON DUPLICATE KEY UPDATE ping_ok=1; | INSERT INTO hosts (name, ping_ok) VALUES ('ServerA', 1) ON DUPLICATE KEY UPDATE ping_ok=1; | ||
| Line 178: | Line 178: | ||
</source> | </source> | ||
=== INSERT ... SELECT === | |||
Used when you want you want include data from another table in an <code>INSERT</code> statement (eg you want to reference a unique ID in another table) | Used when you want you want include data from another table in an <code>INSERT</code> statement (eg you want to reference a unique ID in another table) | ||
<source lang="mysql">INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171');</source> | <source lang="mysql"># Basic insert | ||
INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171'); | |||
# With 'ON DUPLICATE', VALUES allows the result from the SELECT to re-used | |||
INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171') | |||
ON DUPLICATE KEY UPDATE id = VALUES(id);</source> | |||
=== UPDATE ... SELECT === | |||
Here the <code>table</code> table is being updated from the <code>hware</code> table. | Here the <code>table</code> table is being updated from the <code>hware</code> table. | ||
<source lang="mysql">UPDATE table SET make=(SELECT make FROM hware WHERE id=5) WHERE tid=2;</source> | <source lang="mysql">UPDATE table SET make=(SELECT make FROM hware WHERE id=5) WHERE tid=2;</source> | ||