Hi !
)-----Original Message-----
)From: rsaras [mailto:[EMAIL PROTECTED]]
)Sent: Wednesday, August 15, 2001 10:23 PM
)To: [EMAIL PROTECTED]
)Subject: insert records
)
)
)hai
)
) can anybody help me?
)
)Table A
)-----------
)name date
)a 2000/12/12
)b 2000/12/12
)c 2000/12/12
)d 2000/12/12
)
)now i want to take first 2 records from Table A and insert back
)to Table A with different date.
)
)The result should be:-
)
)
)Table A
)-----------
)name date
)a 2000/12/12
)b 2000/12/12
)c 2000/12/12
)d 2000/12/12
)a 2000/12/13
)b 2000/12/13
)
)any idea?
You can make use of INSERT INTO...SELECT syntax, bu the target table of the
INSERT statement cannot appear in the FROM clause of the SELECT part of the
query because it's forbidden in ANSI SQL to SELECT from the same table into
which you are inserting.
So, you can't select the data from the same table and insert back to it.
But, you can have one more dummy table to which you can insert the same data
as the original one and using DATE_ADD(date_data, INTERVAL 1 DAY) you can
re-insert back to the original one as shown below:
mysql> create table t_date1(name text, date1 date);
Query OK, 0 rows affected (0.00 sec)
mysql> create table t_date2(name text, date1 date);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_date1 values('a','2000-08-15');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_date1 values('b','2000-08-15');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_date2 select * from t_date1;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t_date1;
+------+------------+
| name | date1 |
+------+------------+
| a | 2000-08-15 |
| b | 2000-08-15 |
+------+------------+
2 rows in set (0.00 sec)
mysql> select * from t_date2;
+------+------------+
| name | date1 |
+------+------------+
| a | 2000-08-15 |
| b | 2000-08-15 |
+------+------------+
2 rows in set (0.01 sec)
mysql> insert into t_date1 select name, DATE_ADD(date1,INTERVAL 1 DAY) from
t_date2 LIMIT 2;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t_date1;
+------+------------+
| name | date1 |
+------+------------+
| a | 2000-08-15 |
| b | 2000-08-15 |
| a | 2000-08-16 |
| b | 2000-08-16 |
+------+------------+
4 rows in set (0.00 sec)
Here, you can use LIMIT 2 in the first insert itself to insert only first 2
records from t_date1 to t_date2, and later you can just select it.
Hope this helps you
Venu
--
For technical support contracts, go to https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Venu <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Woodside, California USA
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
