Tag: Update

Batch update to replace special characters in SQL Studio Management

I am trying to remove/correct some special characters in my database that show strange behaviour when exported.

Specifically & and the apostrophe ’

I ran a query to find the values that contain the special characters, and while the query succeed at finding them, I still cannot replace/remove them with the second query UPDATE (nothing happens):

SELECT  [Loc1],[IND_KEY] FROM [Database].[dbo].[List] WHERE CONVERT(VARCHAR(MAX), Loc1)LIKE'%[&]%';  UPDATE [Database].[dbo].[List] SET  Loc1 = REPLACE(CAST(Loc1 as nVarchar(4000)), '[&]','and') WHERE Loc1 LIKE '%[&]%';

I am using Microsoft SQL Server Management Studio.

Generic update of “normal” columns with data from JSON

I am trying to do a "generic" insert/update of table columns using JSON. There are tons of info how to update a JSON column, but I have found none that updates several normal columns in a table generic.

Insert columns with JSON data works

This works as I understand it in a generic way:

INSERT INTO inputtable SELECT * FROM json_populate_record (NULL::inputtable,     '{       "id": "0",       "name": "orkb type foo examples tutorials orkb",       "sum": 5743,       "float_col": 94.55681687716474     }' );

Fiddle: https://www.db-fiddle.com/f/eaQG8H4yqY9hnQBZjzJgz/29

Update columns with JSON data?

There are examples of "non-generic" update using JSON, but I am searching for a "generic" solution. In my dreams it should work as this pseudo code:

with data as ( select * from json_each_text(     '{       "id": "0",       "name": "orkb type foo examples tutorials orkb",       "sum": 5743,       "float_col": 95     }') ) update inputtable set = (select * from data) where id=0

Is this possible to do? If it is, how do I do this?

Creating a trigger to update one table’s row based on another update from a different table’s row

So, I have the following two tables:

CREATE TABLE IF NOT EXISTS `services` (   `services_id` INT NOT NULL AUTO_INCREMENT,   `staff_num` INT NOT NULL,   `health_and_wellness` INT NOT NULL,   `tutoring` INT NOT NULL,   PRIMARY KEY (`services_id`),   UNIQUE INDEX `services_id_UNIQUE` (`services_id` ASC) VISIBLE,   INDEX `fk_services_healthandwell_idx` (`health_and_wellness` ASC) VISIBLE,   INDEX `fk_services_tutoring_idx` (`tutoring` ASC) VISIBLE,   CONSTRAINT `fk_services_tutoring`     FOREIGN KEY (`tutoring`)     REFERENCES `tutoring` (`tutoring_id`)     ON DELETE CASCADE     ON UPDATE CASCADE,   CONSTRAINT `fk_services_healthandwell`     FOREIGN KEY (`health_and_wellness`)     REFERENCES `health and wellness` (`health_and_wellness_id`)     ON DELETE CASCADE     ON UPDATE CASCADE) ENGINE = InnoDB;

and

CREATE TABLE IF NOT EXISTS `tutoring` (   `tutoring_id` INT NOT NULL AUTO_INCREMENT,   `subject` INT NOT NULL,   `staff_members` INT NOT NULL,   PRIMARY KEY (`tutoring_id`),   UNIQUE INDEX `tutoring_id_UNIQUE` (`tutoring_id` ASC) VISIBLE,   INDEX `fk_tutoring_subject_idx` (`subject` ASC) VISIBLE,   CONSTRAINT `fk_tutoring_subject`     FOREIGN KEY (`subject`)     REFERENCES `subjects` (`subject_id`)     ON DELETE NO ACTION     ON UPDATE NO ACTION) ENGINE = InnoDB;

I want to create a trigger that updates services.staff_num whenever tutoring.staff_members is updated.. I’ve tried a couple different triggers, but each time nothing happens or I get an error.. My latest attempt was this:

DELIMITER $  $   CREATE TRIGGER UpdateStaffNum AFTER UPDATE ON collegedb.tutoring FOR EACH ROW     BEGIN         UPDATE services         SET services.staff_num = services.staff_num + tutoring.staff_members;  END;

Which of course would be too simple too work, heh. Anyone have any suggestions on this? Thanks.

Is it possible to update the dataset using update_post_meta

I am trying to create two custom fields for image and text. I have two inputs in the loop:

... <input type="hidden" name="wt_set['upload_set_img'][]" id="img" value="100" /> <input type="text" name="wt_set['title'][]" value="Text One" />  <input type="hidden" name="wt_set['upload_set_img'][]" id="img1" value="200" /> <input type="text" name="wt_set['title'][]" value="Text Two" /> ...

The result is this $ _POST[‘wt_set’]

array(2) {   ["upload_set_img"]=>   array(2) {     [0]=>     string(3) "100"     [1]=>     string(3) "200"   }   ["title"]=>   array(2) {     [0]=>     string(3) "Text One"     [1]=>     string(3) "Text Two"   } }

Then I do this:

foreach( $  _POST['wt_set'] as $  name => $  arr_values ){     foreach( $  arr_values as $  i => $  value){         update_post_meta( $  post_id, $  name, $  value );     } }

But I only get one value:

  ["upload_set_img"]=>   array(1) {     [0]=>     string(3) "100"   }   ["title"]=>   array(1) {     [0]=>     string(4) "Text One"   }

I need get_post_meta($ post->ID,'upload_set_img'); and get_post_meta($ post->ID,'title'); equaled:

["upload_set_img"]=>   array(1) {     [0]=>     string(3) "100"     [1]=>     string(3) "200"   }   ["title"]=>   array(1) {     [0]=>     string(4) "Text One"     [1]=>     string(4) "Text Two"   }

How do I properly use update_post_meta to get this result?

MariaDb 10.4 not starting in a freebsd jail after update

ran a pkg update and mariadb seems to be broken. Cant find out what the reason might be after 2h our googling and digging. Help to figure out would be highly appreciated. Thanks in advance. The hostname.err shows no additional entries after DB start (the try to start)

FreeBSD nextcloud 12.2-RELEASE-p6 FreeBSD 12.2-RELEASE-p6 df578562304(HEAD) TRUENAS amd64

mariadb104-client-10.4.19 Multithreaded SQL database (client) mariadb104-server-10.4.19 Multithreaded SQL database (server)

root@nextcloud:~ # service mysql-server start Starting mysql. root@nextcloud:~ # service mysql-server status mysql is not running. root@nextcloud:~ #  root@nextcloud:/tmp # cat /etc/rc.conf cron_flags="$  cron_flags -J 15"  # Disable Sendmail by default sendmail_enable="NO" sendmail_submit_enable="NO" sendmail_outbound_enable="NO" sendmail_msp_queue_enable="NO"  # Run secure syslog syslogd_flags="-c -ss"  # Enable IPv6 ipv6_activate_all_interfaces="YES" apache24_enable="yes" mysql_enable="yes" redis_enable="yes" php_fpm_enable="yes"

Server Startup sh -x /usr/local/etc/rc.d/mysql-server start https://pastebin.com/UdEC8Th6

root@nextcloud:~ # cat /usr/local/etc/mysql/my.cnf # # This group is read both by the client and the server # use it for options that affect everything, see # https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#option-groups # [client-server] port    = 3306 socket  = /tmp/mysql.sock #socket  = /var/run/mysql/mysql.sock # # include *.cnf from the config directory # !includedir /usr/local/etc/mysql/conf.d/

UPDATE SET N + 1 equal to a row (with iteration)

Question: How would you iterate over the previous result (referenced below) over for each of the values in column b3?

MariaDB Fiddle (this includes prior fiddle information to solve the previous question): https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=222f694a36cc41131fe558438e1d6ccd

NOTE: this is a continuation from a problem referenced here: Update SET N + 1 equal to a row
Requesting a solution when adding 1 more dimension of complexity.

Input Table: 3 columns (iteration for each b3)

b1  b2  b3 1   X   P1 2   Z   P1 3   X   P1 4   Y   P1 5   Z   P1 6   X   P1 7   Y   P1 8   X   P2 9   Z   P3 10  X   P3 11  Z   P3 12  X   P3 13  Z   P2 14  Z   P3

Desired Result: 3 columns (iteration for each b3)

b1  b2  b3 1   X   P1 2   Y   P1 3   X   P1 4   Y   P1 5   Z   P1 6   X   P1 7   Y   P1 8   X   P2 9   Z   P3 10  X   P3 11  Y   P3 12  X   P3 13  Y   P2 14  Y   P3

Simple iteration for reference on this data:
— This code increments count and then iterates over each value of b3
— Then sets the resulting values to b2.

UPDATE b JOIN(     SELECT b1,         row_number() over (partition by b3         order by b1) rn FROM b) n on n.b1 = b.b1 SET b2 = rn;