Geek Answers Handbook

Mysql REPLACE query with multiple primary keys

therefore, the MYSQL REPLACE command (not to be confused with the row replacement function) replaces the row if there is a column with the same primary key with data inserted ...

but what if I have two primary keys and I want to use both to specify a string to replace not only one of them ... How to specify mysql to use both keys, and not just one

+5
source share
1 answer

This should not change; this is the same syntax. Just make sure both keys are listed as columns. For instance:

REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` /* , ... */ )
  VALUES ( 'widgets', 14, 'Blue widget with purple trim' );

EDIT

, , . , , !

CREATE SCHEMA `my_testdb`;
USE `my_testdb`;
CREATE TABLE `my_table` (
  `key1` VARCHAR(20) NOT NULL,
  `key2` INTEGER NOT NULL,
  `othercolumn1` VARCHAR(50),
  CONSTRAINT PRIMARY KEY (`key1`, `key2`) );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'Green widget with fuchsia trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'widgets', 15, 'Yellow widget with orange trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'thingamabobs', 14, 'Red widget with brown trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'Blue widget with purple trim' );
SELECT * FROM `my_table`;

:

key1          key2  othercolumn1
widgets       14    Blue widget with purple trim
widgets       15    Yellow widget with orange trim
thingamabobs  14    Red widget with brown trim

, , , :

, , . -MySQL

, , , , . , :

CREATE SCHEMA `my_testdb2`;
USE `my_testdb2`;
CREATE TABLE `my_table` (
  `key1` VARCHAR(20) NOT NULL,
  `key2` INTEGER NOT NULL,
  `color` VARCHAR(20) NOT NULL UNIQUE,
  `othercolumn1` VARCHAR(50),
  CONSTRAINT PRIMARY KEY (`key1`, `key2`) );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'green', 'Green widget with fuchsia trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'widgets', 15, 'yellow', 'Yellow widget with orange trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'thingamabobs', 14, 'red', 'Red widget with brown trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'yellow', 'Yellow widget with purple trim' );
SELECT * FROM `my_table`;

, REPLACE (key1, key2) REPLACE, . BOTH REPLACE, . :

key1          key2  color   othercolumn1
widgets       14    yellow  Yellow widget with purple trim
thingamabobs  14    red     Red widget with brown trim

(key1, key2) ( "", 14) "" - , .

, !

+5

More articles:


All Articles