Devart Forums

Code: Select all

CREATE TABLE `table1` (
  `RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `KEY` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`RECNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `table2` (
  `RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `KEY` varchar(10) NOT NULL DEFAULT '',
  `ID` varchar(23) DEFAULT NULL,
  PRIMARY KEY (`RECNO`),
  UNIQUE KEY `KEY` (`KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `table3` (
  `RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `KEY` varchar(10) NOT NULL DEFAULT '',
  `ID` varchar(23) DEFAULT NULL,
  PRIMARY KEY (`RECNO`),
  UNIQUE KEY `KEY` (`KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Code: Select all

SET @REQ = 'UPDATE table1 t1 INNER JOIN (
		SELECT
			t2_KEY, t3_KEY
		FROM (
			SELECT t2.KEY AS t2_KEY, t2.ID, t3.KEY AS t3_KEY FROM
				(SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table2) t2 INNER JOIN
				(SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table3) t3 ON (t2.ID = t3.ID)
			WHERE
				(t2.ID <> " ")
		) r
		GROUP BY t2_KEY
	) mc ON (t1.KEY = mc.t2_KEY)
SET
	t1.KEY = mc.t3_KEY';
    
PREPARE Req FROM @REQ;
EXECUTE Req;
DEALLOCATE PREPARE Req;

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  Query.SQL.Text := 'SET @REQ = ''UPDATE table1 t1 INNER JOIN ('#13#10 +
    '		SELECT'#13#10 +
    '			t2_KEY, t3_KEY'#13#10 +
    '		FROM ('#13#10 +
    '			SELECT t2.KEY AS t2_KEY, t2.ID, t3.KEY AS t3_KEY FROM'#13#10 +
    '				(SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table2) t2 INNER JOIN'#13#10 +
    '				(SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table3) t3 ON (t2.ID = t3.ID)'#13#10 +
    '		) r'#13#10 +
    '   GROUP BY t2_KEY'#13#10 +
    '	) k ON (t1.KEY = k.t2_KEY)'#13#10 +
    'SET'#13#10 +
    '	t1.KEY = k.t3_KEY'';'#13#10+
    'PREPARE Req FROM @REQ;'#13#10 +
    'EXECUTE Req;'#13#10 +
    'DEALLOCATE PREPARE Req;';

  Query.ExecSQL;
end;