-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathhow-to-update-a-field-in-a-table-with-a-field-from-another-table.html
More file actions
166 lines (159 loc) · 6.87 KB
/
Copy pathhow-to-update-a-field-in-a-table-with-a-field-from-another-table.html
File metadata and controls
166 lines (159 loc) · 6.87 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="chrome=1">
<title>Dreams of thought</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="/theme/styles.css">
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<link rel="stylesheet" href="/theme/slicknav.css">
<script src="/theme/jquery.slicknav.min.js"></script>
<link href="//maxcdn.bootstrapcdn.com/font-awesome/4.2.0/css/font-awesome.min.css" rel="stylesheet">
<script type="text/javascript"
src="http://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML">
</script>
<!--[if lt IE 9]>
<script src="//cdnjs.cloudflare.com/ajax/libs/html5shiv/3.6.2/html5shiv.js"></script>
<![endif]-->
<script>
$(function(){
$('#menu').slicknav({'label':''});
});
</script>
<link rel="icon" href="/theme/favicon.ico" sizes="16x16 32x32 48x48 64x64" type="image/vnd.microsoft.icon">
<link rel="apple-touch-icon" sizes="114x114" href="/theme/apple-touch-icon-precomposed.png">
<link rel="apple-touch-icon" sizes="152x152" href="/theme/favicon152.png">
<link rel="apple-touch-icon" sizes="196x196" href="/theme/favicon196.png">
</head>
<body>
<div id="wrapper">
<header>
<nav class="byline"><ul id="menu">
</ul></nav><!-- /#menu -->
<div class="downloads">
<a href="#" class="fa fa-Twitter"></a>
<a href="#" class="fa fa-Github"></a>
</div>
<hgroup>
<h1>How to update a field in a table with a field from another table</h1>
<footer class="article-footer">
<address class="vcard author">
By <a class="url fn" href="./author/admin.html">admin</a>
</address>
<abbr class="published" title="2010-12-07T15:15:00+05:30">
on 07.12.2010
</abbr>
</footer><!-- /.post-info -->
</hgroup>
<meta name="tags" contents="mysql" />
<meta name="tags" contents="update" />
</header>
<section id="content" class="body article">
<div class="entry-content">
<p>I needed a table which gave the map between the URLs and nodes in
drupal. I ended up creating a table like this :</p>
<div class="line-block">
<div class="line">CREATE TABLE `nid_url_map` (</div>
<div class="line-block">
<div class="line">`nid` int(10) NOT NULL DEFAULT '0',</div>
<div class="line">`url` varchar(255) DEFAULT NULL,</div>
<div class="line">`type` varchar(32) DEFAULT NULL,</div>
<div class="line">PRIMARY KEY (`nid`)</div>
</div>
<div class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8</div>
</div>
<p>To populate this table I used the url_alias table :</p>
<div class="line-block">
<div class="line">CREATE TABLE `url_alias` (</div>
<div class="line-block">
<div class="line">`pid` int(10) unsigned NOT NULL AUTO_INCREMENT,</div>
<div class="line">`src` varchar(128) NOT NULL DEFAULT '',</div>
<div class="line">`dst` varchar(128) NOT NULL DEFAULT '',</div>
<div class="line">`language` varchar(12) NOT NULL DEFAULT '',</div>
<div class="line">`nid` int(11) NOT NULL DEFAULT '0',</div>
<div class="line">PRIMARY KEY (`pid`),</div>
<div class="line">UNIQUE KEY `dst_language` (`dst`,`language`),</div>
<div class="line">KEY `src_language` (`src`,`language`),</div>
<div class="line">KEY `nid` (`nid`)</div>
</div>
<div class="line">) ENGINE=MyISAM AUTO_INCREMENT=267714 DEFAULT CHARSET=utf8</div>
</div>
<p>This is the query I ran at first to populate the table :</p>
<div class="line-block">
<div class="line">INSERT IGNORE INTO nid_url_map (nid,url)</div>
<div class="line">SELECT SUBSTR(src,6) AS nid,dst AS url FROM url_alias</div>
<div class="line">WHERE src LIKE 'node/%';</div>
</div>
<p>The url_alias table maps the internal url ( in the form of node/ID ) to
the pretty URL of the page ( eg. comedy/monty-python-spam ) . Now what
remained was the type. The node type is available in the node table :</p>
<div class="line-block">
<div class="line">CREATE TABLE `node` (</div>
<div class="line-block">
<div class="line">`nid` int(10) unsigned NOT NULL AUTO_INCREMENT,</div>
<div class="line">`vid` int(10) unsigned NOT NULL DEFAULT '0',</div>
<div class="line">`type` varchar(32) NOT NULL DEFAULT '',</div>
<div class="line">`language` varchar(12) NOT NULL DEFAULT '',</div>
<div class="line">`title` varchar(255) NOT NULL DEFAULT '',</div>
<div class="line">`uid` int(11) NOT NULL DEFAULT '0',</div>
<div class="line">`status` int(11) NOT NULL DEFAULT '1',</div>
<div class="line">`created` int(11) NOT NULL DEFAULT '0',</div>
<div class="line">`changed` int(11) NOT NULL DEFAULT '0',</div>
<div class="line">`comment` int(11) NOT NULL DEFAULT '0',</div>
<div class="line">`promote` int(11) NOT NULL DEFAULT '0',</div>
<div class="line">`moderate` int(11) NOT NULL DEFAULT '0',</div>
<div class="line">`sticky` int(11) NOT NULL DEFAULT '0',</div>
<div class="line">`tnid` int(10) unsigned NOT NULL DEFAULT '0',</div>
<div class="line">`translate` int(11) NOT NULL DEFAULT '0',</div>
<div class="line">`titlehash` char(32) DEFAULT '',</div>
<div class="line">PRIMARY KEY (`nid`),</div>
<div class="line">UNIQUE KEY `vid` (`vid`),</div>
<div class="line">KEY `node_changed` (`changed`),</div>
<div class="line">KEY `node_created` (`created`),</div>
<div class="line">KEY `node_moderate` (`moderate`),</div>
<div class="line">KEY `node_promote_status` (`promote`,`status`),</div>
<div class="line">KEY `node_status_type` (`status`,`type`,`nid`),</div>
<div class="line">KEY `node_title_type` (`title`,`type`(4)),</div>
<div class="line">KEY `node_type` (`type`(4)),</div>
<div class="line">KEY `uid` (`uid`),</div>
<div class="line">KEY `tnid` (`tnid`),</div>
<div class="line">KEY `translate` (`translate`),</div>
<div class="line">KEY `title` (`title`),</div>
<div class="line">KEY `titlehash` (`titlehash`)</div>
</div>
<div class="line">) ENGINE=MyISAM AUTO_INCREMENT=197121 DEFAULT CHARSET=utf8</div>
</div>
<p>I referred the MySQL man page on the
<a class="reference external" href="http://dev.mysql.com/doc/refman/5.0/en/update.html">UPDATE</a> query.
Found a useful query in the comments there. In case I need to use this
one again, here's where I'm gonna refer :)</p>
<div class="line-block">
<div class="line">UPDATE nid_url_map,node</div>
<div class="line">SET nid_url_map.type = node.type</div>
<div class="line">WHERE nid_url_map.nid = node.nid;</div>
</div>
<p>There you go, easy peasy :)</p>
</div><!-- /.entry-content -->
<div class="article-share-tags">
<div class="end-article-tags">
<i class="fa fa-tags"></i>
<a href="./tag/mysql.html">mysql</a>
<a href="./tag/update.html">update</a>
</div>
<div class='article-share'>
share -
</div>
</div>
</section>
<div class="neighbors">
</div>
<footer>
<a href="./pages/about.html" >About</a>
<div class="right-footer">
<a href="./categories.html" >Categories</a>
<a href="./tags.html" >Tags</a>
</div>
</footer>
</body>
</html>