Error #1293: There Can Be Only One TIMESTAMP Column With CURRENT_TIMESTAMP In DEFAULT Or ON UPDATE Clause
I am using MySQL 4.0/4.1 version. And I am trying to add two timestamp columns to a single table. The columns are insert_date and updat_date to capture the date/time the record was initially inserted as well as the date/time the record was last updated respectively.
When I try to set one column (insert_date) with a default value of CURRENT_TIMESTAMP and the other column (updat_date) with ON UPDATE CURRENT_TIMESTAMP, I end up getting the following error:
#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
Is there any way around this problem?
View Complete Forum Thread with Replies
Related Forum Messages:
How Set Default To CURRENT_TIMESTAMP?
I have a column of type datetime which I would like to have a default value of CURRENT_TIMESTAMP. I have tried: ALTER TABLE table1 ALTER dateEntered SET DEFAULT CURRENT_TIMESTAMP but this gives a syntax error, probably because it wants a literal at the end there.
View Replies !
UPDATE Changes Unreferenced TIMESTAMP Column
I have a table demo defined like this: >>create table DEMO (ID BIGINT AUTO_INCREMENT PRIMARY KEY , TIMESTAMP TIMESTAMP, STATUS VARCHAR(10)); I insert a couple of values: >> 1, 2007-08-31 10:10:10 >> 2, 2007-08-31 10:10:11 >> 3, 2007-08-31 10:10:12 Then I change the STATUS field with an UPDATE query, even the TIMESTAMP column changest to current_timestamp. UPDATE demo SET status="hello" WHERE id=2; >> 1, 2007-08-31 10:10:10 >> 2, 2007-08-31 09:06:45, hello >> 3, 2007-08-31 10:10:12 I don't understand this behaviour. I havent references the TIMESTAMP column and hence expect it to be unmodified. Can it be that the TIMESTAMP has a DEFAULT_VALUE specification? Probably not, because if I inser another column (DEFVAL INT DEFAULT 5) that colum does not change when updateing the STATUS field - only the TIMESTAMP does.
View Replies !
Remove ON UPDATE For TIMESTAMP Column
I've got a table with a timestamp column that has ON UPDATE CURRENT_TIMESTAMP. I realise what I actually wanted was DEFAULT CURRENT_TIMESTAMP since most of the time I don't want an update to change the timestamp (only an insert). I can't figure out how I'm supposed to remove the ON UPDATE clause of the column.
View Replies !
How To Read Column Attributes Like ON UPDATE CURRENT TIMESTAMP Or UNSIGNED
I am trying to alter my tables from my database via an selfbuild frontend system. Everything is fine, except the following. When altering the columns/fields in my table, i can access almost all information with "SHOW FULL COLUMNS FROM tablename", but i can't get any information about the so called "Attributes" like: - ON UPDATE CURRENT TIMESTAMP - UNSIGNED ZEROFILL - UNSIGNED - BINARY
View Replies !
Prevent ON UPDATE CURRENT_TIMESTAMP
Does anybody know if there is a way to prevent an ON UPDATE CURRENT_TIMESTAMP from occuring if I don't want it to? Only way I can see to do it is by altering the column properties to just a TIMESTAMP field, do the update, then alter the column back.
View Replies !
Error 1054: Unknown Column Xxxx In WHERE Clause..
I'm trying to run this query $uneRequete = "SELECT * FROM Usager WHERE motDePasse = {$loginPassword}"; and I get this error message : Error 1054: Unknown column 'xxxx' in WHERE clause..... where 'xxxx' is the content of $loginPassword I don't know what is going on.
View Replies !
Error 1054 <42S22>: Unknown Column 'user' In 'where Clause'
am trying too delete a user from mysql database (which is used primarly for mediawiki). I typed in the following command in mysql command line client use wikidb; database changed Delete from user where User='Admin'; Too which I recieved the error Error 1054 <42S22>: Unknown column 'user' in 'where clause'
View Replies !
Timestamp Default Value
I am using: mysql Ver 12.22 Distrib 4.0.18, for sun-solaris2.9 (sparc) And I do this: mysql> create table test (a timestamp default null, b timestamp default null); Query OK, 0 rows affected (0.03 sec) mysql> desc test; +-------+---------------+------+-----+----------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+----------------+-------+ | a | timestamp(14) | YES | | NULL | | | b | timestamp(14) | YES | | 00000000000000 | | +-------+---------------+------+-----+----------------+-------+ 2 rows in set (0.00 sec) Can anyone tell my why the default value for column 'b' is not NULL as well?
View Replies !
Datetime And Timestamp As Default
I would like a column created_timestamp to be populated on record creation (and preferably not updatable); and a column updated_timestamp to be populated on record creation, and then automatically updated on any update. should created_timestamp be of type datetime? and updated_timestamp be of type timestamp? and is ordering of the columns an issue (i.e. only the first gets updated)? mysql complains on the following? create table history ( name varchar(20) not null, createdtimestamp datetime not null default current_timestamp, updatedtimestamp timestamp not null, primary key(name) );
View Replies !
How To Set Default Values For Timestamp Fields
what is the syntax for specifying the default values for timestamps in MySQL in the CREATE TABLE command? I have 2 timestamps, one for when the record was entered, the other for any subsequent updates. I know that the first timestamp column will be set automatically on updates, but how does the other for creation timestamp get set. My app is not generating the SQL directly so setting the value using the NOW() command is not an option, as the inserts are coming from different timezones I cannot let the clients set their own values.
View Replies !
Set Default Values For Timestamp Fields
what is the syntax for specifying the default values for timestamps in MySQL in the CREATE TABLE command? I have 2 timestamps, one for when the record was entered, the other for any subsequent updates. I know that the first timestamp column will be set automatically on updates, but how does the other for creation timestamp get set. My app is not generating the SQL directly so setting the value using the NOW() command is not an option, as the inserts are coming from different timezones I cannot let the clients set their own values.
View Replies !
Default Current Timestamp Issues
Using MySQL 4.1.7. I have a sole TIMESTAMP column in the table: insert_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, Looking at the timestamp docs, a query with NULL or empty, should cause the column to use the current time for its default value, but is not automatically updated. However, putting NULL or simply leaving blank in an INSERT query, gives 01/01/0001. Any ideas?
View Replies !
DATE / TIMESTAMP Default Values
im is having problems with auto inserting date in my table. I dont know what is the default value i will set if column type to DATE. It was succesful though if is use the TIMESTAMP type in the column and is using CURRENT_TIMESTAMP as the dafault value. mysql automatically inserts the current date and time to the database everytime a new record is inserted. The problem with using TIMESTAMP is that i only want the date and not the additional time. Is there a default value I can use for the DATE function instead of using TIMESTAMP? I already tried NOW(), CURDATE(), CURRENT_DATE() to no avail
View Replies !
Update INT UNIQUE Column - Duplicate Entry Error
As an example, I'm creating a table which holds following values in MySql 5.0: idcol lft (unique) 1 1 2 2 3 3 4 4 -> when I try to update the lft column using : UPDATE unique_example set lft = lft +1 where lft > 1; I get following error message : Duplicate entry '3' for key 2 Exactly the same approach works using SQL server 2000. Apparently, MySQL processes the rows one by one, as it throws an error on the UNIQUE lft column from whom I wish to update all rows at once? Code:
View Replies !
Changing The Default Time Zone Of A Current Timestamp In MySQL
I have a shared host which uses the time zone MST (GMT-7), and I cannot seem to change the time zone for my own database. What is the best way to create a timestamp field in a table that has the default value be the current time in GMT-0? Is setting the default value of the timestamp field to "DATE_SUB(NOW(), INTERVAL -7 HOUR)"
View Replies !
Timestamp Update
In my table, there is a field which is a timestamp. Problemo here is whenever I update the other fields, this timestamp field always resets to now. So i lose the actual timestamp before the update.
View Replies !
Update And Timestamp
mysql 4.1 UPDATE `table` SET `col1` = 'smth1', `col2` = 'smth2' WHERE `col3` ='smth3'; There is a TIMESTAMP field I want to preserve. Last update command turns all dates to 'NOW'. What do I have to add to keep original TIMESTAMP?
View Replies !
Auto Update Timestamp
Here is my example: I have 3 fields. Field 1 is a unix timestamp which is 0000-00-00 00:00:00 by default. Field 2 is a number integer which is 30 by default Field 3 is a unix timestamp which is a random date by default When Field 1 is populated or updated, I would like to add the value in Field 2 (30) as days to recreate Field 3 with a timestamp equal to Field 1, plus 30 days (or whatever is listed in Field 2)
View Replies !
Timestamp - To Update When The Row Is Updated.
i would like to have a table with ( among other things ) a time stamp column that holds the time that the row was created. this seemed easy enough by just executing ALTER TABLE mytable ADD stampcolumn timestamp; but the problem with this is that the time stamp updates for a given row any time that row is updated. I would like to have a timestamp that just stays at its inital value of the row insertion time. is this possible? how?
View Replies !
Error 1064 Restoring PhpMyAdmin Dump (error On Foreign Key Clause)
I am getting an Error 1064 when I try to restore a dump I created using phpMyAdmin from an online database into a newly created database on my home system. The error points to a section of the dump file that adds foreign key constraints. I've already disabled the foreign key checks. The problem seems to occur only with tables in which I have more than one foreign key. The dump file foreign key constraint section looks very odd to me because multiple ALTER TABLE statements to add foreign keys are separated only by commas, not semi-colons, and even weirder, each time a new statement to add a foreign key constraint is created, it is then repeated in the creation of the next foreign key constraint. Code:
View Replies !
Timestamp Column
In a table I have a series of rows that, amongst other things, have a timestamp column. What I would like to do is select a specific row from the timestamp information (easy bit), but then I want to also collect the five previous entries by date/time.
View Replies !
UPDATE WHERE Clause
My query:Code: UPDATE details SET approved=1 WHERE pkey=17 AND pkey=18 doesn't work and doesn't return an error. I can remove everything after AND and it works fine, updating the record with pkey=17. My ultimate goal is to update multiple rows at once. This query is dynamically generated and may contain multiple ANDs. Is this wrong? Should I be doing something diffrently?
View Replies !
Update A Table In A Timestamp Field
I would like to update "field A" in a table in which "field B" is a timestamp. I would like to update all fields whose date is less than a given date. This is the command I was using to no avail: UPDATE my_table SET Field_A=50, WHERE filed_B <= '2005-05-12 15:08:44'; MySQL is returning the following error: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE filed_B <= '2005-05-12 15:08:44'' at line 3 My Host is using MySQL 4.0.25. Can anyone point me in the right direction? What am I doing wrong?
View Replies !
TIMESTAMP :: Creation And Update Date
I want to create an auto creation date and update date in my mysql table. I am using the "MySQL Cookbook" approach, using the TIMESTAMP field which basically is: - create 2 new fields in the table (t_create and t_update) - both NULL - when a new record is created they both will register a common timestamp - when the record is modified the t_update will change, but not the t_create
View Replies !
Use Now() As Default Value For A Column
I cannot find any good material on this, and it seems like it should be so easy. I have two columns in my table, creation_date and last_modified_date. I want the creation_date field to bear the default value of Now(), so that when a row is created the timestamp is there. This value should never change, and should only be set on row creation. The last_modified_date field I want to change on the UPDATE only. It seems that this may be possible using the TIMESTAMP type and the default values there but what I read is that they will update everytime a row is altered. This would be dead-simple if I could just specify Now() as the default value.
View Replies !
Default Value For A Column, When Value Is Not Specified
We have migrated our database from DB2 to MYSQL. In DB2, there is an option like 'GENERATED BY DEFAULT AS IDENTITY' which will cause DB2 to generate unique values for the identity column during insert operations if no value is specified for the identity column. However, if a value is specified for the identity column, DB2 will use it in the insert operation. In MYSQL, is there an option like this?
View Replies !
Default Value On A Column
Hey guys, my hope is to quickly move from SQL2005 to mySQL and i'm hitting the GUID roadblock. If mySQL can't do a GUID by default on a column, I've got to rewrite a ton of my application around that. Tell me with 5.0 this is possible now? Or that there's some cool work-around?
View Replies !
Update All Instances Of A Field To Default Value
Is there a statement or way in MySQL to update a several fields in a database to the default value? Basically reseting the value with out deleting it. I can go through an manually do that by looking at the number and manually setting it to that but it would be easier to just say: Update table set field=default That didn't work but that's what I'm trying to do. While at it is there any easy way to do it with PHPMYADMIN which is what I manage my DB's with.
View Replies !
Store Timestamp In A Column
I'm using PHP and found it unreliable when using the time() function passed to a variable and then inserted in to the database - I can't understand why, but I think the best solution would be for me to use the timestamp function in MySQL. How do I do this though? What is the syntax if I have a table called mytbl and a column name called t with type timestamp.
View Replies !
How To Store Timestamp In A Column
I'm using PHP and found it unreliable when using the time() function passed to a variable and then inserted in to the database - I can't understand why, but I think the best solution would be for me to use the timestamp function in MySQL. How do I do this though? What is the syntax if I have a table called mytbl and a column name called t with type timestamp.
View Replies !
Update Command Without Where Clause
Can anyone help me to recover or undo this problem? I did inadvertently run a command, 'update ..set' without where clause and changed every records. Unfortunately our backup didn't run last night. Is there any way to recover or undo this?
View Replies !
Default Value On Date Column
Using MySQL control center, I created a MySQL database table with a column of Date type. It always give the a Default value (0000-00-00) even null is allowed. I tried remove the default value and it comes back by itself. So if I don't supply a value when insert, the default is used. Query IS NOT NULL will not exclude this record. Now, In VB (with ADO/MyOLEDB), I did the exact query trying to filter out the null record. But I still get the record just like in Control Center. BUT, the value on this column is "NULL". How can I leave the Date field as null if no value is supplied?
View Replies !
Column Default Value Setting
I've got a column called 'articleheader' in a table called 'article'. It currently has a default value of NULL, but i want to set it to default to "(none)" (without the double quotes). I have tried searching to find a solution but everytime I try the posted "solutions" I get an error. I think it is because I don't specifiy the type of column it is or something.
View Replies !
Extend Fields With UPDATE Clause
I don't know why MySQL handles the UPDATE-query not in that way it should be. To show the problem we assume to tables: DROP TABLE IF EXISTS temp1; CREATE TABLE temp1 ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, text1 VARCHAR(45) NOT NULL, PRIMARY KEY(id) ); INSERT INTO temp1 Values ( 1, "" ), ( 2, "" ), ( 3, "" ), ( 4, "" ), ( 5, "" ); DROP TABLE IF EXISTS temp2; CREATE TABLE temp2 ( id_fk INTEGER UNSIGNED NOT NULL, text2 VARCHAR(45) NOT NULL ); INSERT INTO temp2 Values ( 1, "1-1" ), ( 1, "1-2" ), ( 2, "2-1" ), ( 2, "2-2" ), ( 3, "3-1" ), ( 3, "3-2" ), ( 4, "4-1" ), ( 5, "5-1" ); Now I want to update temp1.text2 with all referenced values from temp2.text2 UPDATE temp1 INNER JOIN temp2 ON id = id_fk SET temp1.text1 = Concat( temp1.text1, ',', temp2.text2 ); The table temp1 should (actually) have this content: SELECT * FROM temp1; id text1 1 ,1-1,1-2 2 ,2-1,2-2 3 ,3-1,3-2 4 ,4-1 5 ,5-1 But when I look at the table: SELECT * FROM temp1; id text1 1 ,1-1 2 ,2-1 3 ,3-1 4 ,4-1 5 ,5-1 In doc is mentioned that every line is updated, but he doesn't.
View Replies !
You Can't Specify Target Table '' For Update In FROM Clause
I have problem deleting rows from table with subquery referring to the same table. I store tree structure in the table (there is ParentID field for each row that specifies primary key of the parent row). I'm trying to delete all children of the given row which doesn't have their own children. I can't do this from the script by iterating through all rows because there could be thousands of such records. I thought that the following query will do that: DELETE P FROM `tree` AS P WHERE P.`ParentID` = @parent AND NOT EXISTS (SELECT * FROM `tree` AS C WHERE C.`ParentID` = P.`ID`) But this doesn't work, throwing error: You can't specify target table 'tree' for update in FROM clause How can I make this work?
View Replies !
You Can't Specify Target Table '...' For Update In FROM Clause
I have a table called acct that contains the following fields among others: id,acctid,value,processed Processed=0 Not processed Processed=1 Currently processing Processed=2 processed In need the ability for a program to check to see if a particular acctid has been processed, if it hasn't it sets Processed=1 processes the account then sets Processed=2 It is common that there will be multiple records for the same acctid and I only want each acctid to be processed once. I through this would work but I got the error at the bottom. UPDATE acct SET Processed = '2' WHERE acctid = ( SELECT acctid from acct where Processed = '2' GROUP BY acctid) #1093 - You can't specify target table 'acct' for update in FROM clause
View Replies !
How To Set The Default For A Datetime Column To Curdate()
I'd like to set the default value for a datetime type column in a table to today's date. It seems I should be able to do this with the CURDATE() function. I tried adding a call to the function in the default value field when creating the table's column, by when I save the table the default reverts to "0000-00-00 00:00:00" I've done this in other RDMSs. How can I do it in MySQL?
View Replies !
Column Alias And HAVING Clause
I am having problems with the HAVING clause. I know it can use aliases but the alias I am using has a space in it, eg 'Device ID' My querry looks like (simplified, devID is a calculation) SELECT devID AS 'Device ID' FROM tblDevice HAVING 'Device ID' = '123' and it does not work but if I use SELECT devID AS 'Device_ID' FROM tblDevice HAVING Device_ID = '123' everything works. How can I use an alias that contains a space in the HAVING clause?
View Replies !
Column Alias In WHERE Clause
I wana select 2 fields of a table as a variable and then in that query use that variable like this SELECT money1+money2 AS money WHERE money > 10000 but mysql return an sysntax error Is there any way to use a varible like this in a query?
View Replies !
Using Column Alias Name In WHERE Clause
I am giving alias name to the column in SELECT clause and when I am using same alias name in WHERE cluase, it is giving error. We can use alias names in WHERE clause. Is there any solution or other way to achieve same functionality? QUERY: select concate(firstname, " ", lastname) as name from user where name = "Chetan Parekh";
View Replies !
Using Virtual Column In WHERE Clause
SELECT IF( some_id > 3, 'foo', 'bar' ) AS 'virtualcolumn' FROM table WHERE virtualcolumn = 'foo' MySQL will complain that 'virtualcolumn' is unknow in WHERE clause. However, if I have another query like SELECT IF( some_id > 3, 'foo', 'bar' ) AS 'virtualcolumn' FROM table ORDER BY virtualcolumn ASC .
View Replies !
|
TRACKER
