Тема: ON DUPLICATE KEY UPDATE

Потрібно створити рядок (id, 1, 10) а якщо він є то апдейтнути його на (id, 1, 4)

Таблиця

CREATE TABLE `_Location` (
  `_id` INT NOT NULL AUTO_INCREMENT,
  `_KeyToUsers` INT NULL,
  `_Location` VARCHAR(45) NULL,
  PRIMARY KEY (`_id`),
  INDEX `_KeyToUsers_idx` (`_KeyToUsers`),
  CONSTRAINT `_KeyToUsers`
    FOREIGN KEY (`_KeyToUsers`)
    REFERENCES `_Users` (`_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Спроба 1

INSERT INTO _Location
(_id, _KeyToUsers, _Location )
VALUES
(_id, 1, 10) 
ON DUPLICATE KEY UPDATE    
_KeyToUsers = 1,
_Location = 4;

Спроба 2

INSERT INTO _Location
(_id, _KeyToUsers, _Location )
VALUES
((SELECT _id FROM _Location WHERE _KeyToUsers = 1 limit 1), 1, 10) 
ON DUPLICATE KEY UPDATE    
_KeyToUsers = 1,
_Location = 4;

_KeyToUsers це унікальне число, по ньому потрібно шукати
_id - ключ автоінкремента (не відоме)
_Location - це значення яке потрібно постійно буде міняти

звичайно 4 і 10 числа для прикладу в реальності можуть бути будь які.

Ще раз поясню

Мені потрібно вставити значення _Location якщо не існує рядка з _KeyToUsers ключем, а якщо існує то замінити в рядку _Location 

_KeyToUsers - ключ
_Location значення
_id - невідоме, автоінкремента

Як це зробити ?

ON DUPLICATE KEY UPDATE

Можливо ON DUPLICATE KEY UPDATE тут ні до чого, я раніше не працював з цією командою

2 Востаннє редагувалося koala (15.09.2020 13:32:55)

Re: ON DUPLICATE KEY UPDATE

Вибачте, не зовсім зрозумів.
У вас є таблиця. Унікальне в ній лише поле id, тобто може бути кілька рядків (1, 1, 10), (2, 1, 10)  і т.д. Якщо є такий рядок - то його (усіх їх?) треба оновити на (*, 1, 4), а як немає - додати, правильно?
ON DUPLICATE KEY працюватиме лише якщо ключ збігається, а не решта полів.

3

Re: ON DUPLICATE KEY UPDATE

koala написав:

Унікальне в ній лише поле id

Ні, унікальне ще _KeyToUsers

Це зв'язок "один до одного"

PRIMARY KEY _id теж унікальне, але воно служить для інших цілей... програма яка витягує запит має обов'язково отримати _id

Це НЕ зв'язок "один до багатьох".

koala написав:

Якщо є такий рядок - то його треба оновити на (*, 1, 4), а як немає - додати, правильно?

Так. Якщо він буде, то лише один

4

Re: ON DUPLICATE KEY UPDATE

Betterthanyou написав:

Це зв'язок "один до одного"

Тоді може варто зробити його NOT NULL UNIQUE? Бо дивний "один до одного" виходить.

Подякували: fed_lviv, Betterthanyou2

5

Re: ON DUPLICATE KEY UPDATE

Гм. А чим узагалі така таблиця відрізняється від поля

_Users._Location VARCHAR(45) NULL

?

6 Востаннє редагувалося Betterthanyou (15.09.2020 14:17:15)

Re: ON DUPLICATE KEY UPDATE

Тим що вона може розширюватися в процесі роботи (можливо потім туди будуть додані нові колонки та зовнішні ключі). А якщо поле закинути в _Users потім буде проблематично додавати нові колонки

7

Re: ON DUPLICATE KEY UPDATE

Потрібно створити рядок [...] а якщо він є то апдейтнути його на [...]

Пропоную перевірити чи ваша база підтримує оператор merge, це дасть змогу зекономити декілька рядків коду.

8

Re: ON DUPLICATE KEY UPDATE

Така конструкція працює тільки з primary key.
Вам треба щось інсерт селект зробити.

Вам треба щось типу такого. На працездатність не провіряв.

INSERT INTO _Location
(_KeyToUsers, _Location )
Select 1, ifnull((select 4 from _Location where _KeyToUsers = 1), 10)

9

Re: ON DUPLICATE KEY UPDATE

frz написав:

Потрібно створити рядок [...] а якщо він є то апдейтнути його на [...]

Пропоную перевірити чи ваша база підтримує оператор merge, це дасть змогу зекономити декілька рядків коду.

Ні, не підтримує. В мене MySQL

10

Re: ON DUPLICATE KEY UPDATE

Якщо гарантовано буде один рядок — може, викинути _id і поставити _KeyToUsers і PRIMARY, і FOREIGN? Тоді й ON DUPLICATE KEY  працюватиме як слід.

11

Re: ON DUPLICATE KEY UPDATE

koala написав:

Якщо гарантовано буде один рядок — може, викинути _id і поставити _KeyToUsers і PRIMARY, і FOREIGN? Тоді й ON DUPLICATE KEY  працюватиме як слід.

На жаль викидати _id не можна воно потрібне для програми яка буде виконувати ці запити.

12

Re: ON DUPLICATE KEY UPDATE

Vo_Vik написав:

Така конструкція працює тільки з primary key.
Вам треба щось інсерт селект зробити.

Вам треба щось типу такого. На працездатність не провіряв.

INSERT INTO _Location
(_KeyToUsers, _Location )
Select 1, ifnull((select 4 from _Location where _KeyToUsers = 1), 10)

Цей варіант постійно вставляє.

Пробував ще таке

IF (select count(_id) from _Location where _KeyToUsers = 1, 

INSERT INTO _Location(_KeyToUsers, _Location ) VALUES(1, 4)
,

UPDATE _Location
SET _Location=10
WHERE _KeyToUsers=1

);

Але If працює лише з процедурами

13 Востаннє редагувалося fed_lviv (15.09.2020 14:45:03)

Re: ON DUPLICATE KEY UPDATE

Не дуже зрозумів всі умови:

Betterthanyou написав:

`_KeyToUsers` INT NULL,

Betterthanyou написав:

_KeyToUsers це унікальне число, по ньому потрібно шукати

Betterthanyou написав:

унікальне ще _KeyToUsers

Це зв'язок "один до одного"

То тут так і хочеться ту унікальність прописати :)

Відповідно все норм спрацює (звісно якщо я вірно зрозумів вимоги):
If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY

CREATE TABLE `_Location` (
  `_id` INT NOT NULL AUTO_INCREMENT,
  `_KeyToUsers` INT UNIQUE,
  `_Location` VARCHAR(45) NULL,
  PRIMARY KEY (`_id`),
  INDEX `_KeyToUsers_idx` (`_KeyToUsers`),
  CONSTRAINT `_KeyToUsers`
    FOREIGN KEY (`_KeyToUsers`)
    REFERENCES `_Users` (`_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
INSERT INTO _Location
(_id, _KeyToUsers, _Location )
VALUES
(_id, 1, 10) 
ON DUPLICATE KEY UPDATE    
_Location = 4;

P.S. А про "один до одного" я б гарно подумав перед тим аби його робити. Звісно всяке буває, але краще добре поміркувати чи варто...

Подякували: koala, Betterthanyou2

14 Востаннє редагувалося Betterthanyou (15.09.2020 14:57:12)

Re: ON DUPLICATE KEY UPDATE

fed_lviv написав:

Не дуже зрозумів всі умови:

Betterthanyou написав:

`_KeyToUsers` INT NULL,

Betterthanyou написав:

_KeyToUsers це унікальне число, по ньому потрібно шукати

Betterthanyou написав:

унікальне ще _KeyToUsers

Це зв'язок "один до одного"

То тут так і хочеться ту унікальність прописати :)

Відповідно все норм спрацює (звісно якщо я вірно зрозумів вимоги):
If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY

CREATE TABLE `_Location` (
  `_id` INT NOT NULL AUTO_INCREMENT,
  `_KeyToUsers` INT UNIQUE,
  `_Location` VARCHAR(45) NULL,
  PRIMARY KEY (`_id`),
  INDEX `_KeyToUsers_idx` (`_KeyToUsers`),
  CONSTRAINT `_KeyToUsers`
    FOREIGN KEY (`_KeyToUsers`)
    REFERENCES `_Users` (`_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
INSERT INTO _Location
(_id, _KeyToUsers, _Location )
VALUES
(_id, 1, 10) 
ON DUPLICATE KEY UPDATE    
_Location = 4;

P.S. А про "один до одного" я б гарно подумав перед тим аби його робити. Звісно всяке буває, але краще добре поміркувати чи варто...

Красно дякую!!!

15 Востаннє редагувалося koala (15.09.2020 14:55:37)

Re: ON DUPLICATE KEY UPDATE

Betterthanyou написав:

Дуже дякую!!!

Дякувати — дієслово, що керує давальним відмінком. Дякую (кому?). На відміну від російської і білоруської, де "спасибо" і "дзякуй" є іменниками. Відповідно, треба дякувати або дужо, або дужому, залежно від сенсу, який ви в це вкладаєте, пряма калька з "большое спасибо" тут помилкова. Утім, так узагалі не кажуть — дякують щиро, ґречно, красно, файно, уклінно. Ще можна бути дуже (глибоко, вельми) вдячним.
Можете не дякувати.

Подякували: Betterthanyou1

16

Re: ON DUPLICATE KEY UPDATE

Виправив

Подякували: koala1