MTK MTK -4 years ago 141
MySQL Question

mysql (copy) insert into from select and add one alias column

I have 2 tables

test_table:

id data_text
1 *some text*


undo_test_table:

id data_text modification_date


.

CREATE TRIGGER undo_trigger BEFORE UPDATE
ON test_table
FOR EACH ROW

INSERT INTO test_table_undo SELECT *,NOW() AS modification_date
FROM test_table
WHERE id = NEW.id


What I try with that, is if one column on first table is modified then the trigger (undo_trigger) is fired and copy entire row to another table (test_table_undo)

But in the second table (test_table_undo) I have one more column (modification_date)

The problem:

The above example working good but I don't know how to make that working if I change the (modification_date) column at first place instead as last place in (test_table_undo)
like this:

undo_test_table:

modification_date id data_text


I have tried :

SELECT NOW() AS modification_date,* FROM test_table ...


instead

SELECT *,NOW() AS modification_date FROM test_table ...


But got sintax error.

I think i need some like an UNION but I don't know how I can use NOW() AS modification_date in one UNION

Answer Source

You need to qualify the * with the table's name:

SELECT NOW() AS modification_date, t.* 
-- Here ---------------------------^
FROM   test_table
-- etc...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download