update the id of a value in one mysql table which is the actual in another my sql table-CodePudding

I have two mysql tables, table A and Table B. Table B contains publication names and ids. Table A contains reference information and id of the publication from the publication name.

I need to update some values in Table A, I am able to do it for rest of the values using simple update command:

UPDATE reference SET issue ='4', 
  doi ='', 
  url ='https://app.dimensions.ai/details/publication/pub.1077608314' ,
  issn=''  
where id = 9433356;

though I also need to update one of the column, publication. Now I have to value of the publication. I was wondering if I can write this in one single query, where i insert the value in table B or if the value exists in table b, get the id and update the value in table A.

Table A definitions:

CREATE TABLE `reference` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`end_page` int(11) DEFAULT NULL,
`number` int(11) DEFAULT NULL,
`published` datetime DEFAULT NULL,
`reference_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`start_page` int(11) DEFAULT NULL,
`volume` int(11) DEFAULT NULL,
`project` int(11) NOT NULL,
`publication_source` int(11) NOT NULL,
`seed` tinyint(1) NOT NULL DEFAULT '0',
`accession_number` varchar(128) COLLATE utf8_bin DEFAULT NULL,
`database_name` varchar(1024) COLLATE utf8_bin DEFAULT NULL,
`has_full_text` tinyint(1) NOT NULL DEFAULT '0',
`external_id` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`model_seed` varchar(128) COLLATE utf8_bin DEFAULT 'NONE',
`url` varchar(2083) COLLATE utf8_bin DEFAULT NULL,
`doi` varchar(1024) COLLATE utf8_bin DEFAULT NULL,
`issue` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`issn` varchar(1024) COLLATE utf8_bin DEFAULT NULL,
`call_number` varchar(2083) COLLATE utf8_bin DEFAULT NULL,
`work_type` varchar(1024) COLLATE utf8_bin DEFAULT NULL,
`notes` varchar(10000) COLLATE utf8_bin DEFAULT NULL,
`custom_tags` blob,
PRIMARY KEY (`id`),
KEY `FK_reference_project` (`project`),
KEY `FK_reference_publication_source` (`publication_source`),
CONSTRAINT `FK_reference_project`
  FOREIGN KEY (`project`)
  REFERENCES `project` (`id`),
CONSTRAINT `FK_reference_publication_source`
  FOREIGN KEY (`publication_source`)
  REFERENCES `publicationsource` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10512575 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Table B definition

CREATE TABLE publicationsource ( 
id int(11) NOT NULL AUTO_INCREMENT, 
name varchar(128) COLLATE utf8_bin DEFAULT NULL, 
type varchar(16) COLLATE utf8_bin DEFAULT NULL, 
PRIMARY KEY (id), 
KEY UK_publicationsource_type_name (type,name) 
) ENGINE=InnoDB AUTO_INCREMENT=10921959 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CodePudding user response：

You can't do the UPDATE of reference in the same statement if you need to INSERT a new row to publicationsource. You would have to do the INSERT and then do the UPDATE. You can get the latest auto-increment id generated by that INSERT using LAST_INSERT_ID():

INSERT INTO publicationsource ...; 

UPDATE reference
SET r.issue ='4', 
  doi ='', 
  url ='https://app.dimensions.ai/details/publication/pub.1077608314' ,
  issn='',
  publication_source = LAST_INSERT_ID()
WHERE id = 9433356;

If the publicationsource row you need already exists, then you can use a scalar subquery to get it:

UPDATE reference
SET r.issue ='4', 
  doi ='', 
  url ='https://app.dimensions.ai/details/publication/pub.1077608314' ,
  issn='',
  publication_source = (
    SELECT id FROM publicationsource
    WHERE type = ? AND name = ? LIMIT 1
  )
WHERE id = 9433356;

CodePudding user response：

We can run a query to create the entry in the parent table id it does not already exist before running our insert or update query on the references table.
This means that we can run the same queries, without errors, whether the entries exist or not.

INSERT INTO publicationsource
 (id, name, type)
SELECT DISTINCT 
  1, 
  'publication name 1',
  'type name 1'
WHERE NOT EXISTS
(SELECT id 
FROM publicationsource
WHERE id = 1);

✓

INSERT INTO reference 
(id, publication_source, project, issue, doi, url, issn) VALUES
(9433356, 1, 1, '4', '', 'https://app.dimensions.ai/details/publication/pub.1077608314', '' ) 
ON DUPLICATE KEY UPDATE    
issue ='4', doi ='', url ='https://app.dimensions.ai/details/publication/pub.1077608314' ,issn='';

✓

SELECT * FROM project;

| id |
| -: |
|  1 |

SELECT * FROM publicationsource;

id | name               | type       
-: | :----------------- | :----------
 1 | publication name 1 | type name 1

SELECT * FROM reference;

     id | end_page | number | published | reference_id | start_page | volume | project | publication_source | seed | accession_number | database_name | has_full_text | external_id | model_seed | url                                                          | doi | issue | issn | call_number | work_type | notes | custom_tags
------: | -------: | -----: | :-------- | :----------- | ---------: | -----: | ------: | -----------------: | ---: | :--------------- | :------------ | ------------: | :---------- | :--------- | :----------------------------------------------------------- | :-- | :---- | :--- | :---------- | :-------- | :---- | :----------
9433356 |     null |   null | null      | null         |       null |   null |       1 |                  1 |    0 | null             | null          |             0 | null        | NONE       | https://app.dimensions.ai/details/publication/pub.1077608314 |     | 4     |      | null        | null      | null  |

db<>fiddle here