mysql insert into select join - copy values from one column to another table, passing through a conn-CodePudding

I can't get this to work

CREATE TABLE `oc_tax_class` (
  `tax_class_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `date_added` datetime NOT NULL,
  `date_modified` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `oc_tax_rate`
--

CREATE TABLE `oc_tax_rate` (
  `tax_rate_id` int(11) NOT NULL,
  `geo_zone_id` int(11) NOT NULL DEFAULT 0,
  `name` varchar(255) NOT NULL,
  `rate` decimal(15,4) NOT NULL DEFAULT 0.0000,
  `type` char(1) NOT NULL,
  `date_added` datetime NOT NULL,
  `date_modified` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `oc_tax_rule`
--

CREATE TABLE `oc_tax_rule` (
  `tax_rule_id` int(11) NOT NULL,
  `tax_class_id` int(11) NOT NULL,
  `tax_rate_id` int(11) NOT NULL,
  `based` varchar(10) NOT NULL,
  `priority` int(5) NOT NULL DEFAULT 1
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

3 tables. I want oc_tax_class.title = oc_tax_rate.name I believe, although I'm not sure, that I should

INSERT INTO oc_tax_class(title)

UPDATE oc_tax_class SET title = ...

SELECT oc_tax_rate.name, oc_tax_rule.tax_class_id
JOIN oc_tax_rule ON oc_tax_rate.tax_rate_id = oc_tax_rule.tax_rate_id

And then I don't know what to do next. I need to copy values from one column to another table, passing through a connecting table.

CodePudding user response：

MySQL supports a multi-table UPDATE syntax, but the documentation (https://dev.mysql.com/doc/refman/en/update.html) has pretty sparse examples of it.

In your case, this may work:

UPDATE oc_tax_class 
JOIN oc_tax_rule USING (tax_class_id)
JOIN oc_tax_rate USING (tax_rate_id)
SET oc_tax_class.title = oc_tax_rate.name;

I did not test this. I suggest you test it first on a sample of your data, to make sure it works the way you want it to.