B
B
BonBon Slick2020-01-30 09:54:26
MySQL
BonBon Slick, 2020-01-30 09:54:26

Batch / Bulk update Mysql?

There are no such problems in PGSql.

UPDATE sinch_sms_recipient
SET 
  send_status = custom_values.send_status,
  htpp_status = custom_values.http_status
  
FROM (
  VALUES
    ('phone_num_1', '1234567812345678', 'Delivered', 0),
    ('phone_num_2', '1234567812345678', 'Queued', 200),
    ('phone_num_3', '1234567812345678', 'Delivered', 200),
    ('phone_num_4', '1234567812345678', 'Delivered', 0),
    ('phone_num_5', '1234567812345678', 'Dispatched', 404),
) AS custom_values (phone_number, batch_id, send_status,, http_status)

WHERE sinch_sms_recipient.batch_id = custom_values.batch_id
AND
WHERE sinch_sms_recipient.phone_number = custom_values.phone_number


And in MS even I do not catch up with how to do it right.

INSERT INTO
  sinch_sms_recipient (send_status, http_status)
VALUES
  ('Delivered', 404),
  ('Delivered', 404) ON DUPLICATE KEY
UPDATE
  send_status =
VALUES(send_status),
  http_status =
VALUES(http_status);

This will insert a new record without PK with send_status = Delivered and http_status = 127. Some nonsense.

This is hardly suitable, as I understand it, CASE can only choose one thing, you can’t do it
CASE phone = 1 AND batch_id = 213 THEN status = Delivered AND http = 404

UPDATE a 
SET fruit = (CASE id WHEN 1 THEN 'apple'
                     WHEN 2 THEN 'orange'
                     WHEN 3 THEN 'peach'
             END)
WHERE id IN(1,2 ,3);


We have a phone and a batch_id, we need to go through and update the data in the table in batches. PK is composite, consists of phone and batch_id. PRIMARY KEY (batch_id, phone).
Input example
[
[
 batch_id = 1234567812345678,
 phone = 1234567890,
 htpp_status = 404,
 deliverey_status  = Delivered
],
[
 batch_id = 1234567812345678,
 phone = 0987654321,
 htpp_status = 404,
 deliverey_status  = Delivered
]
]

Here is a batch of data, you need to update the statuses under phone + batch_id. phone exists for other batch_id's as well.

Did according to the guide
and answers like tacos

Answer the question

In order to leave comments, you need to log in

1 answer(s)
D
DevMan, 2020-01-30
@BonBonSlick

the scheme with indexes where? anonymous Vangi on vacation.
odku works on a unique index (as hinted at on duplicate key). if not, then there is no issue.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question