MySQL: Expanding table columns and insert/update values

MySQL

I need to combine two tables into one.

Table s_ogr

id | ad | numara | yurt_id |

Current Table s_kontrol

id | ogr_id | yurt_id

What is needed s_kontrol_k

id | ogr_id | ogr_numara |ogr_ad | yurt_id  `(ogr_id represent the id from s_ogr)`

( ad = name , yurt_id = department_id, ogr_id is student_number(reason of problem, should be student_id) )

My Sittuation; s_kontrol is full of data. I need it to be like s_kontrol_k , but new two fields should be taken from s_ogr table , by the match operation of s_ogr and s_kontrol (s_ogr.id=s_kontrol.ogr_id and s_ogr.yurt_id =s_kontrol.yurt_id). Previous mistake was not to add the ogr_id as student_id into s_kontrol

How to create my new s_kontrol_k with filled values of new columns. I am able to copy the same values from s_kontrol but could'n achieve to fill new colums

Best Answer

As far as I can understand what you want is

    CREATE TABLE IF NOT EXISTS `s_kontrol_k` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `ogr_id` int(11) NOT NULL,
      `ogr_ad` varchar(255) NOT NULL,
      `ogr_numara` varchar(255) NOT NULL,
      `yurt_id` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    );

    INSERT INTO `s_kontrol_k` (
        SELECT sk.id,sk.ogr_id, so.ad AS ogr_ad,
        so.numara AS ogr_numara, so.yurt_id FROM s_kontrol AS sk
        INNER JOIN s_ogr AS so ON sk.ogr_id = so.id
    );

First create the desired table s_kontrol_k and copy data from both s_ogr and s_kontrol to newly created table s_kontrol_k.

I hope this helps..

Related Solutions

Mysql – How should I update ID field values for all records based on previous values

A couple options come to mind. If you have a charish data type:

update table set column=concat('999',column);

If you have an integer type find the max value, let's say it's under 10,000 then

update table set column=9990000+column

Mysql – filling a table by triggering multiple tables on insert

The trigger code you just displayed in your question needs to be manually defined on each table you intend to copy from. For tables x, y and z the answer to your question is:

CREATE TRIGGER some_name AFTER INSERT ON `x` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);
CREATE TRIGGER some_name AFTER INSERT ON `y` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);
CREATE TRIGGER some_name AFTER INSERT ON `z` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);

I suggest you make table m use MyISAM to prevent all potential deadlocks.

UPDATE 2013-10-15 19:39 EDT

First, define the table m like this:

CREATE TABLE m
(
    id int(11) not null auto_increment, //PK
    name varchar(100) null,
    something_else varchar(100) default 'not important',
    primary key (id),
    unique key (name)
) ENGINE = MyISAM;

Then, make the trigger like this:

CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = 'new thing'
;

I would suggest that make something_else have the date and time as well

CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = CONCAT('new thing ',NOW());
;

and maybe the table where the insert came from

CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = CONCAT('new thing from x : ',NOW());
;

Give it a Try !!!

Best Answer

Related Solutions

Mysql – How should I update ID field values for all records based on previous values

Mysql – filling a table by triggering multiple tables on insert

UPDATE 2013-10-15 19:39 EDT

Related Question