이 포스트를 보낸곳 ()
12.2.11. UPDATE Syntax
Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
For the single-table syntax, the
UPDATE statement updates columns of
existing rows in tbl_name with new values. The
SET clause indicates which columns to modify
and the values they should be given. Each value can be given as an
expression, or the keyword DEFAULT to set a
column explicitly to its default value. The
WHERE clause, if given, specifies the
conditions that identify which rows to update. With no
WHERE clause, all rows are updated. If the
ORDER BY clause is specified, the rows are
updated in the order that is specified. The
LIMIT clause places a limit on the number of
rows that can be updated.
For the multiple-table syntax,
UPDATE updates rows in each table
named in table_references that satisfy
the conditions. In this case, ORDER BY and
LIMIT cannot be used.
where_condition is an expression that
evaluates to true for each row to be updated. It is specified as
described in Section 12.2.8, “SELECT Syntax”.
The UPDATE statement supports the
following modifiers:
-
If you use the LOW_PRIORITY keyword,
execution of the UPDATE is
delayed until no other clients are reading from the table.
This affects only storage engines that use only table-level
locking (MyISAM, MEMORY,
MERGE).
-
If you use the IGNORE keyword, the update
statement does not abort even if errors occur during the
update. Rows for which duplicate-key conflicts occur are not
updated. Rows for which columns are updated to values that
would cause data conversion errors are updated to the closest
valid values instead.
If you access a column from tbl_name in
an expression, UPDATE uses the
current value of the column. For example, the following statement
sets the age column to one more than its
current value:
UPDATE persondata SET age=age+1;
Single-table UPDATE assignments are
generally evaluated from left to right. For multiple-table
updates, there is no guarantee that assignments are carried out in
any particular order.
If you set a column to the value it currently has, MySQL notices
this and does not update it.
If you update a column that has been declared NOT
NULL by setting to NULL, the column
is set to the default value appropriate for the data type and the
warning count is incremented. The default value is
0 for numeric types, the empty string
('') for string types, and the
“zero” value for date and time types.
UPDATE returns the number of rows
that were actually changed. The
mysql_info() C API function
returns the number of rows that were matched and updated and the
number of warnings that occurred during the
UPDATE.
You can use LIMIT
row_count to restrict the
scope of the UPDATE. A
LIMIT clause is a rows-matched restriction. The
statement stops as soon as it has found
row_count rows that satisfy the
WHERE clause, whether or not they actually were
changed.
If an UPDATE statement includes an
ORDER BY clause, the rows are updated in the
order specified by the clause. This can be useful in certain
situations that might otherwise result in an error. Suppose that a
table t contains a column id
that has a unique index. The following statement could fail with a
duplicate-key error, depending on the order in which rows are
updated:
UPDATE t SET id = id + 1;
For example, if the table contains 1 and 2 in the
id column and 1 is updated to 2 before 2 is
updated to 3, an error occurs. To avoid this problem, add an
ORDER BY clause to cause the rows with larger
id values to be updated before those with
smaller values:
UPDATE t SET id = id + 1 ORDER BY id DESC;
You can also perform UPDATE
operations covering multiple tables. However, you cannot use
ORDER BY or LIMIT with a
multiple-table UPDATE. The
table_references clause lists the
tables involved in the join. Its syntax is described in
Section 12.2.8.1, “JOIN Syntax”. Here is an example:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
The preceding example shows an inner join that uses the comma
operator, but multiple-table UPDATE
statements can use any type of join allowed in
SELECT statements, such as
LEFT JOIN.
You need the UPDATE privilege only
for columns referenced in a multiple-table
UPDATE that are actually updated.
You need only the SELECT privilege
for any columns that are read but not modified.
If you use a multiple-table UPDATE
statement involving InnoDB tables for which
there are foreign key constraints, the MySQL optimizer might
process tables in an order that differs from that of their
parent/child relationship. In this case, the statement fails and
rolls back. Instead, update a single table and rely on the
ON UPDATE capabilities that
InnoDB provides to cause the other tables to be
modified accordingly. See
Section 13.2.5.4, “FOREIGN KEY Constraints”.
Currently, you cannot update a table and select from the same
table in a subquery.
User Comments
Table A
+--------+-----------+
| A-num | text |
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
+--------+-----------+
Table B:
+------+------+--------------+
| B-num| date | A-num |
| 22 | 01.08.2003 | 2 |
| 23 | 02.08.2003 | 2 |
| 24 | 03.08.2003 | 1 |
| 25 | 04.08.2003 | 4 |
| 26 | 05.03.2003 | 4 |
I will update field text in table A
with
UPDATE `Table A`,`Table B`
SET `Table A`.`text`=concat_ws(”,`Table A`.`text`,`Table B`.`B-num`,” from “,`Table B`.`date`,’/’)
WHERE `Table A`.`A-num` = `Table B`.`A-num`
and come to this result
Table A
+--------+------------------------+
| A-num | text |
| 1 | 24 from 03 08 2003 / |
| 2 | 22 from 01 08 2003 / |
| 3 | |
| 4 | 25 from 04 08 2003 / |
| 5 | |
——–+————————-+
(only one field from Table B is accepted)
But i will come to this result
Table A
+--------+--------------------------------------------+
| A-num | text |
| 1 | 24 from 03 08 2003 |
| 2 | 22 from 01 08 2003 / 23 from 02 08 2003 / |
| 3 | |
| 4 | 25 from 04 08 2003 / 26 from 05 03 2003 / |
| 5 | |
+--------+--------------------------------------------+
UPDATE TABLE_1 LEFT JOIN TABLE_2 ON TABLE_1.COLUMN_1= TABLE_2.COLUMN_2
SET TABLE_1.COLUMN = EXPR WHERE TABLE_2.COLUMN2 IS NULL
An outerjoin is performed based on the equijoin condition.
Records not matching the equijoin from table2 are marked with null.
This facilitates to update table1 column with expression whose corresponding value from table2 is returned as NULL
update tab2, tab1 set tab1.name=tab2.name,
tab2.name=tab1.name where tab1.id=tab2.id
The
former leads to the values of column “name” in two tables are equal and
equal to the original tab2.name. The latter, however, swaps the values
of column “name” in table tab1 with tab2.
If update (in two or
more tables in ONE sql) many columns simultaneously by means of more
complicated algorithm, eg., the value of one column is determined by
other serval columns, things are worse. I can not find the clear rule
described as above, and have to write the sql in two sentances
eventurally. ABSOLUTELY it is a bug.
As a preparation to sql
store procedure, multi-table update can drastically accelerate the bulk
update operation. Such an important feature added in version 4.0 is so
dangerous that you should verify the correctness of every mutili-table
update sql. MySQL develop group dare declare 4.0.15 is the standard
version! :(
update t1, t2 set t1.field = t2.value where t1.this = t2.that;
If UPDATE gives an error like this:
“You are using safe update mode and you tried to update a table without…etc.”
…then
it may be that your .cnf file must be edited to disable safemode. This
worked for me. In order for the change in the .cnf file to take effect,
you must have permission to restart mysqld in the server OS
environment. There is a page in the online documentation that explains
safe mode entitled ‘safe Server Startup Script’.
Create TEMPORARY Table Groups (Id INTEGER AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(31), GroupId VARCHAR(31), ValWithinGroup INTEGER);
INSERT INTO Groups VALUES (null, “Davy”, “Boy”, 2);
INSERT INTO Groups VALUES (null, “Mary”, “Girl”, 2);
INSERT INTO Groups VALUES (null, “Bill”, “Boy”, 5);
INSERT INTO Groups VALUES (null, “Jill”, “Girl”, -3);
INSERT INTO Groups VALUES (null, “Fred”, “Boy”, 3);
# Find the lowest value for each group
CREATE TEMPORARY TABLE GroupSum AS SELECT GroupId, MIN(ValWithinGroup)
AS baseVal FROM Groups GROUP BY GroupId;
# create an index so mySQL can efficiently match
ALTER TABLE GroupSum ADD UNIQUE (GroupId);
# finally, make the baseline adjustment
UPDATE Groups LEFT JOIN GroupSum USING (GroupId)
SET ValWithinGroup=ValWithinGroup-baseVal;
SELECT * FROM Groups;
# 1 Davy Boy 0
# 2 Mary Girl 5
# 3 Bill Boy 3
# 4 Jill Girl 0
# 5 Fred Boy 1
#Each group (“Boy”, “Girl”) now has a (lowest) ValWithinGroup entry of 0.
Notes:
That index addition is necessary because on larger tables mySQL would
rather die than figure to (internally) index a single column join.
I was not able, using mySQL 4.1.1, to do this as a subquery:
UPDATE
Groups LEFT JOIN (SELECT GroupId, MIN(ValWithinGroup) AS baseVal FROM
Groups GROUP BY GroupId) AS GrpSum USING (GroupId) SET
ValWithinGroup=ValWithinGroup-baseVal;
Csaba Gabor
I
had a problem – a had to update a column “rate” but if the existince or
new value is greater then 5 this “5” will be finally value in field.
So, I do it in one “magick” query ;)
Here an example:
“3” is a some value, from form or something
update item
set rate = case when round((rate+3)/2) < 6 then round((rate+3)/2) else 5 end
where id = 1 and rate <= 6;
greetings
pecado
UPDATE xoops_bb_posts_text
SET post_text=(
REPLACE (post_text,
‘morphix.sourceforge.net’,
‘www.morphix.org’));
using
the string function REPLACE, all items in the post_text column with
‘morphix.sourceforge.net’ get this substring replaced by
‘www.morphix.org’. Ideal when writing a script is just too much effort.
update some_table
set col = col + 1
where key = ‘some_key_value’
and @value := col
The @value := col will always evaluate to true and will store the col value before the update in the @value variable.
You could then do
select @value;
in order to see what the value was before you updated it
update Table1 t1
join Table2 t2 on t1.ID=t2.t1ID
join Table3 t3 on t2.ID=t3.t2ID
set t1.Value=12345
where t3.ID=54321
UPDATE table_name SET name = CONCAT(forename, ‘ ‘, surname)
Senario is, ID 8 has multiple records, only the last (highest) record needs to be changed
update t1 set c1 = ‘NO’
where id=’8′
order by recno desc limit 1
I would prefer update t1 set c1=’NO’ WHERE ID=8 AND RECNO = (SELECT MAX(RECNO) FROM T1 WHERE ID=8)
But that’s not currently allowed
UPDATE table1 SET table1field = (SELECT MAX(table2.table2field) FROM table2 WHERE table1.table1field = table2.table2field)
This
can be helpful if you need to create a temporary table storing an ID
(for, say, a person) and a “last date” and already have another table
storing all dates (for example, all dates of that person’s orders).
Additional information on MySQL correlated subqueries is at http://dev.mysql.com/doc/mysql/en/correlated-subqueries.html
update item
set rate = case when round((rate+3)/2) < 6 then round((rate+3)/2) else 5 end
where id = 1 and rate <= 6;
I think it can be done simpler with LEAST:
update item
set rate = least(round((rate+3)/2), 5)
where id = 1 and rate <= 6;
(I could be wrong, but that looks like it ought to work.)
while TRUE {
..UPDATE table SET value = 1
….WHERE value = 0 and name = ‘name’
..if no. of rows affected > 0, break
..else wait and try again
}
The
code above waits until the semaphore is “cleared” (value = 0) and then
“sets” it (value = 1). When done, you “clear” the semaphore by
UPDATE table SET value = 0 WHERE name = ‘name’
The
assumption is that the UPDATE is “atomic” in that no concurrent access
by another process can occur between testing and setting the value
field.
A very server resources friendly method to update
multiple rows in the same table is by using WHEN THEN (with a very
important note).
UPDATE tbl_name SET fld2 = CASE fld1
WHEN val1 THEN data1
WHEN val2 THEN data2
ELSE fld2 END
The
note is: do not forget ELSE. If you do not use it, all rows that are
outside the range of your updated values will be set to blank!
e.g.
A table that contains entries of different categories, in which an
internal order needs to represented ( lets say a table with busstops on
different routes). If you add new entries or move stops from one route
to another you will most likely want to increment the position of the
busstop within this route. That’s how you can do it
table busstops
id | route | busstop | pos
1 | 1 | A | 1
2 | 1 | B | 2
3 | 1 | C | 3
4 | 2 | C | 1
5 | 2 | D | 2
6 | 2 | A | 3
7 | 2 | E | 4
8 | 2 | F | 5
9 | 2 | G | 6
10 | 2 | H | 7
Moving D,E,F,G To route 1
SET @pos=(SELECT max(t1.pos) FROM busstops t1 WHERE t1.route = 1 );
UPDATE busstops SET pos = ( SELECT @pos := @pos +1 ), route =1 WHERE id IN (5,7,8,9)
I
doubt this could be done otherwise since referencing the table you wish
to update within the subquery creates circular references
After
DELETE or UPDATE i.e. when a row of a subset is lost/deleted/moved away
from it, the whole subset will need to be reordered. This can be done
similarily :
SET @pos=0;
UPDATE busstops SET pos = ( SELECT @pos := @pos +1 ) WHERE route = 1 ORDER BY pos ASC
Chris H (chansel0049)
A is a normal table, B is a temporary table:
Worked in 4
update A, B set A.population=B.pop_count where A.id=B.id
In version 5, however, the above query only updated one element while still matching “all”
In 5 I had to do it like this:
update A RIGHT JOIN B on A.id=B.id set A.population=B.pop_count
Updates all population counts correctly.
[edit: RIGHT JOIN not LEFT JOIN…]
Just
adding to Mike Zhange’s comment above, the problem seems to occur in
single table update as well. Update’s effects are reflected immediately
(as in intra-statement):
create table a (id int, v1 float, v2 float);
insert into a values(1,1,2);
update a SET v1=v1/(v1+v2), v2=v1/(v1+v2);
select * from a;
Actual Result: 1 0.333333 0.142857
Expected Result: 1 0.333333 0.333333
UPDATE table SET f1=’foo’, f2=
IF(f3=value,‘one’,IF(f3=value_bis,’two’,f2))
WHERE f5=’afected’
This set the values of field ‘f2’ according to the values of field ‘f3’ in the rows field f5 ‘afected’.
create table a (id int, v1 float, v2 float);
insert into a values(1,1,2);
update a SET v1=v1/(v1+v2), v2=v1/(v1+v2);
select * from a;
Actual Result:* 1 0.333333 0.142857
Expected Result: 1 0.333333 0.333333
This
works exactly to the specifications of the documentation. the value of
v1 is changed to 0.333333 before the equation to update v2 is
evaluated, so the expected result is 0.142857
UPDATE jobs AS toTable, jobs AS fromTable
SET
toTable.job_type_id = fromTable.job_type_id,
toTable.job_company_id = fromTable.job_company_id,
toTable.job_source = fromTable.job_source,
WHERE
(toTable.job_id = 6)
AND
(fromTable.job_id = 1)
————–
Pretty
cool. What I’m doing here is copying the information I need from the
row where job_id=1 to the row where job_id=6, on the same table.
update t1,t2 set t1.field=t2.value where t1.this=t2.that;
That strikes me as an elegant syntax. Here is the closest I could come up with for doing that on Oracle:
update t1 set t1.field=(select value from t2 where t1.this=t2.that) where t1.this in (select that from t2);
That strikes me as convoluted by comparison.
Matt Ryan Writes :
>>Here’s a workaround for the update/subquery/cant do self >>table “bug”
>>Senario is, ID 8 has multiple records, only the last
>>(highest) record needs to be changed
>>update t1 set c1 = ‘NO’
>>where id=’8′
>>order by recno desc limit 1
You can also accomplish the same by the following query :
update t1 , (select id ,max(recno) as recno from t1 where id=8 group by recno) tt
set t1.c1 = ‘NO’
where tt.id=t1.id and
t1.recno=tt.recno
Comments are welcome.
> update some_table
> set col = col + 1
> where key = ‘some_key_value’
> and @value := col
> The @value := col will always evaluate to true and will store the col value before the update in the @value variable.
In
fact, in won’t if `col` is NULL (0, empty string etc.) – then the
condition is not met and the update query won’t be processed. The
correct condition would be:
AND ((@value := `col`) OR (1 = 1))
It was very helpful to me anyway. Thx Justin!
A
summary table (in this case created to hold summary counts of other
genealogy data, based on the two fields that make up the PRIMARY key)
often contains unique key fields and one or more summary totals (Cnt in
this case). Additional ranking fields in the summary table can be
easily updated to contain rankings of the Cnt field using the IF
function and
local variables.
Table DDL:
CREATE TABLE `countsbyboth` (
`SurnameID` int(11) unsigned NOT NULL default ‘0’,
`GedID` int(11) unsigned NOT NULL default ‘0’,
`Cnt` int(11) unsigned NOT NULL default ‘0’,
`sRank` int(11) unsigned NOT NULL default ‘0’,
`nRank` int(11) unsigned NOT NULL default ‘0’,
PRIMARY KEY (`SurnameID`,`GedID`),
KEY `SurnameID` (`SurnameID`,`Cnt`),
KEY `GedID` (`GedID`,`Cnt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
After populating the table with rows containing key and summary data (and leaving the rank field(s) to be updated in
a subsequent step), the rank fields can be updated using syntax similar to the following:
update countsbyboth set srank=0, nrank=0;
set @rnk:=1, @gedid=0;
update countsbyboth
set srank=if(@gedid=(@gedid:=gedid), (@rnk:=@rnk+1),(@rnk:=1))
order by gedid desc, cnt desc;
set @rnk:=1, @snmid=0;
update countsbyboth
set nrank=if(@snmid=(@snmid:=surnameid), (@rnk:=@rnk+1),(@rnk:=1))
order by surnameid desc, cnt desc;
Query OK, 11752 rows affected (0.08 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 11752 rows affected (0.24 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 11752 rows affected (0.19 sec)
It
looks convoluted, but is really quite simple. The @rnk variable needs
to be initialized, and the keyval variable (in this case @gedid or
@snmid) needs to be set to a value that will not be matched by the
first record. The IF() function checks the previous key value (left
side) against the current key value (right side), and either increments
the @rnk variable when the desired key value is the same as the
previous records, or reset the @rnk variable to 1 when the key value
changes.
This can be easily extended to accomodate ranking on
more than one key value, and does not require sub-selects that take
considerable resources for a large table.
This example
intentionally assigns different ranks to equal values of Cnt for a
given key, to facilitate reporting where column headings contain the
rank value.
AND ((@value := `col`) OR (1 = 1))
gets optimised out as ‘true’ and @value is left as NULL after the update.
I got it to work again by rewriting as
update some_table
set col = col + 1
where key = ‘some_key_value’
and ((@value := col) IS NULL OR (@value := col) IS NOT NULL)
So
you get a true value either way and value will get set. Be careful what
you put on the right-hand-side as it could get evaluated twice.
This is true but there are two simple ways around this limit.
1) nest the subquery 2 deep so it is fully materialized before the update runs. For example:
Update t1 set v1 = t3.v1 where id in
(select t2.id, t2.v1 from (select id, v1 from t1) t2) t3
2) use a self join rather than a subquery
When
updating one table using values obtained from another table, the manual
describes the “update table1, table2” syntax, but does not delve into
the correlated subquery approach very much. It also does not point out
a VERY important execution difference.
Consider the following script:
======================================================
drop table if exists test_1;
drop table if exists test_2;
CREATE TABLE test_1 (
col_pk integer NOT NULL,
col_test integer
);
alter table test_1 add PRIMARY KEY (col_pk);
CREATE TABLE test_2 (
col_pk_join integer NOT NULL,
col_test_new integer
);
insert into test_1 (col_pk, col_test) values ( 1, null );
insert into test_1 (col_pk, col_test) values ( 2, null );
commit;
insert into test_2 (col_pk_join, col_test_new) values ( 1, 23 );
insert into test_2 (col_pk_join, col_test_new) values ( 1, 34 );
insert into test_2 (col_pk_join, col_test_new) values ( 2, 45 );
commit;
select * from test_1;
select * from test_2;
# This update should NOT work, but it does.
UPDATE test_1 t,
test_2 tmp
set t.col_test = tmp.col_test_new
where t.col_pk = tmp.col_pk_join;
commit;
select * from test_1;
======================================================
The output of the select and update statements is:
+--------+----------+
| col_pk | col_test |
+--------+----------+
| 1 | NULL |
| 2 | NULL |
+--------+----------+
2 rows in set
+-------------+--------------+
| col_pk_join | col_test_new |
+-------------+--------------+
| 1 | 23 |
| 1 | 34 |
| 2 | 45 |
+-------------+--------------+
3 rows in set
Query OK, 2 rows affected
Rows matched: 2 Changed: 2 Warnings: 0
Query OK, 0 rows affected
+--------+----------+
| col_pk | col_test |
+--------+----------+
| 1 | 23 |
| 2 | 45 |
+--------+----------+
2 rows in set
Note
that the update did NOT produce any errors or warnings. It should have.
Why? Because a join on value 1 produces two values from table test_2.
Two values cannot fit into a space for one. What MySQL does in this
case is use the first value and ignore the second value. This is really
bad in my opinion because it is, in essence, putting incorrect data
into table test_1.
Replace the update statement above with:
UPDATE test_1 t1
set t1.col_test = (
select col_test_new
from test_2 t2
where t1.col_pk = t2.col_pk_join
)
;
This will produce the appropriate error for the given data:
“ERROR 1242 : Subquery returns more than 1 row”
and will not perform any update at all, which is good (it protects table test_1 from getting bad data).
Now
if you have different data……..if you comment out one of the “1”
values inserted into table test_2 and use the correlated subquery
update instead of the multi-table update, table test_1 will get updated
with exactly what you expect.
The moral of this
example/tip/bug-report: do not use the multi-table update. Use the
correlated subquery update instead. It’s safe. If you keep getting an
error when you think you shouldn’t, you either have bad data in your
source table or you need to rework your subquery such that it produces
a guaranteed one-row result for each destination row being updated.
The
reason I call the multi-table update a bug is simply because I feel it
should produce the same or similar error as the correlated subquery
update. My hope is that MySQL AB will agree with me.
/* INICIO del script */
#En caso de tener con NOT NULL alguna columna (Ejemplo)
ALTER TABLE MiTabla CHANGE columna
columna MEDIUMINT UNSIGNED DEFAULT NULL;
#Cambio todos los valores a NULL (para que no haya riesgo de valores duplicados con restricciones UNIQUE)
UPDATE MiTabla SET columna=NULL;
#Declaro una variable como contador (puede ser 1,2,3… o el num desde donde queremos empezar)
SET @c:=1;
#Consulta
UPDATE MiTabla SET columna=(SELECT @c:=@c+1);
#Ahora podemos usar ALTER TABLE nuevamente si queremos cambiar la columna a NOT NULL (en caso de que la hayamos cambiado)
/* FIN del script */
Tengan
en cuenta de que los indices principales (los declarados como PRIMARY
KEY, por ejemplo, o los que se usan para linquear tablas) NO DEBERIAN
CAMBIARSE, ya que se estropearian los vinculos entre las tablas! Esto
podria evitarse declarando las claves foraneas (FOREIGN KEY) de las
tablas de linqueo con el valor ON UPDATE CASCADE (lo que al actualizar
los indices refrescaria los links entre las tablas).
update sample_table set shift_1 = case when shift_1=’A’ then ‘C’ when shift_1=’B’ then ‘A’ when shift_1=’C’ then ‘B’ end
We’ll come up with several enhanced queries in sooner..
For any explanations, feel free to contact us @ reachmeras@gmail.com
Cheers,
Rasu
update db1.a, (
select distinct b.col1, b.col2
from db2.b, db2.c, db2.d
where b.col1<>” and d.idnr=b.idnr and c.user=d.user and c.role=’S’
order by b.col1) as e
set a.col1 = e.col1
where a.idnr = e.col1
The
point is that every select statement returns a table. Name the result
and you can access its columns. In this example I called the result ‘e’.
I never could figure out how to set the value of
multiple columns with nesting a select statement dedicated to each
column. Now I’ve got it. I’m attaching a transcript of doing it both
ways. The statements use the tables that already exist in the mysql
schema (at least in 5.0), so you can easily recreate this on your box
in a test schema.
————–
DROP TABLE IF EXISTS test
————–
Query OK, 0 rows affected (0.00 sec)
————–
CREATE TABLE test (t_id INT,k_id INT, t_name CHAR(64), t_desc TEXT) AS
SELECT help_topic_id AS t_id, help_keyword_id AS k_id, NULL AS t_name, NULL AS t_desc FROM mysql.help_relation LIMIT 10
————–
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
————–
SELECT * FROM test
————–
+------+------+--------+--------+
| t_id | k_id | t_name | t_desc |
+------+------+--------+--------+
| 0 | 0 | NULL | NULL |
| 327 | 0 | NULL | NULL |
| 208 | 1 | NULL | NULL |
| 409 | 2 | NULL | NULL |
| 36 | 3 | NULL | NULL |
| 388 | 3 | NULL | NULL |
| 189 | 4 | NULL | NULL |
| 169 | 5 | NULL | NULL |
| 393 | 6 | NULL | NULL |
| 17 | 7 | NULL | NULL |
+------+------+--------+--------+
10 rows in set (0.00 sec)
————–
######
## This is the elegant single select solution! ##
######
UPDATE test AS t, (SELECT * FROM mysql.help_topic) AS h SET
t.t_name=h.name,
t.t_desc=substr(h.url,1-locate(‘/’,reverse(h.url)))
WHERE t.t_id=h.help_topic_id
————–
Query OK, 10 rows affected (0.04 sec)
Rows matched: 10 Changed: 10 Warnings: 0
————–
SELECT * FROM test
————–
+------+------+------------------+---------------------------+
| t_id | k_id | t_name | t_desc |
+------+------+------------------+---------------------------+
| 0 | 0 | JOIN | join.html |
| 327 | 0 | SELECT | select.html |
| 208 | 1 | REPEAT LOOP | repeat-statement.html |
| 409 | 2 | ISOLATION | set-transaction.html |
| 36 | 3 | REPLACE INTO | replace.html |
| 388 | 3 | LOAD DATA | load-data.html |
| 189 | 4 | CREATE FUNCTION | create-function.html |
| 169 | 5 | CHANGE MASTER TO | change-master-to.html |
| 393 | 6 | CHAR | string-type-overview.html |
| 17 | 7 | SHOW COLUMNS | show-columns.html |
+------+------+------------------+---------------------------+
10 rows in set (0.03 sec)
————–
DROP TABLE IF EXISTS test
————–
Query OK, 0 rows affected (0.00 sec)
————–
CREATE TABLE test (t_id INT,k_id INT, t_name CHAR(64), t_desc TEXT) AS
SELECT help_topic_id AS t_id, help_keyword_id AS k_id, NULL AS t_name, NULL AS t_desc FROM mysql.help_relation LIMIT 10
————–
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
————–
SELECT * FROM test
————–
+------+------+--------+--------+
| t_id | k_id | t_name | t_desc |
+------+------+--------+--------+
| 0 | 0 | NULL | NULL |
| 327 | 0 | NULL | NULL |
| 208 | 1 | NULL | NULL |
| 409 | 2 | NULL | NULL |
| 36 | 3 | NULL | NULL |
| 388 | 3 | NULL | NULL |
| 189 | 4 | NULL | NULL |
| 169 | 5 | NULL | NULL |
| 393 | 6 | NULL | NULL |
| 17 | 7 | NULL | NULL |
+------+------+--------+--------+
10 rows in set (0.00 sec)
————–
######
## This is the nasty one select for each column that needs to be updated method! ##
######
UPDATE test AS t SET
t.t_name=(SELECT name FROM mysql.help_topic WHERE t.t_id=help_topic_id),
t.t_desc=(SELECT substr(url,1-locate(‘/’,reverse(url))) FROM mysql.help_topic WHERE t.t_id=help_topic_id)
————–
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10 Changed: 10 Warnings: 0
————–
SELECT * FROM test
————–
+------+------+------------------+---------------------------+
| t_id | k_id | t_name | t_desc |
+------+------+------------------+---------------------------+
| 0 | 0 | JOIN | join.html |
| 327 | 0 | SELECT | select.html |
| 208 | 1 | REPEAT LOOP | repeat-statement.html |
| 409 | 2 | ISOLATION | set-transaction.html |
| 36 | 3 | REPLACE INTO | replace.html |
| 388 | 3 | LOAD DATA | load-data.html |
| 189 | 4 | CREATE FUNCTION | create-function.html |
| 169 | 5 | CHANGE MASTER TO | change-master-to.html |
| 393 | 6 | CHAR | string-type-overview.html |
| 17 | 7 | SHOW COLUMNS | show-columns.html |
+------+------+------------------+---------------------------+
10 rows in set (0.00 sec)
Bye
summary(X,A,B,C,D) and a query which returns: (X,E,F) and
you want to update the summary table fields C and D with the values of
E and F:
Summary: (1,2,3,0,0),(10,12,13,0,0) and query result:
(1,4,5),(10,14,15) should result in the updated summary table:
(1,2,3,4,5,6),(10,11,12,13,14,15)
BAD SOLUTION (same query is evaluated twice!):
UPDATE summary SET C=(SELECT E FROM (query) q WHERE summary.X=q.X), D=(SELECT F FROM (query) q WHERE summary.X=q.X)
GOOD SOLUTION (query is only evaluated once):
UPDATE summary AS t, (query) AS q SET t.C=q.E, t.D=q.F WHERE t.X=q.X
+-------+------+-------+-------+
| index | name | item1 | item2 |
+-------+------+-------+-------+
| 1 | one | 25 | 50 |
| 2 | two | 75 | 40 |
| 3 | one | 35 | 60 |
| 4 | four | 100 | 80 |
+-------+------+-------+-------+
4 rows in set (0.00 sec)
### (@olditem1:=item1) will assign the value of item1 *before* the update.
mysql> update test set item1=item2,item2=@olditem1 where (@olditem1:=item1) and item1>item2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from test;
+-------+------+-------+-------+
| index | name | item1 | item2 |
+-------+------+-------+-------+
| 1 | one | 25 | 50 |
| 2 | two | 40 | 75 |
| 3 | one | 35 | 60 |
| 4 | four | 80 | 100 |
+-------+------+-------+-------+
4 rows in set (0.00 sec)
mysql 3년쓰면서 업데이트문도 헷갈린다 -_-