Stuart Felenstein <[EMAIL PROTECTED]> wrote on 26/11/2004 13:54:31:
> I'm trying to build an insert query that will add a
> value into a field , after the first insert query adds
> the record.
>
> Relevant table information:
>
> +-----------------+---------+------------+----------+
> | RecordID | InitOn | LenChoice | EndDate |
> + [PrimID,AutoInc]| [Date] | [int] | [Date] |
> +-----------------+---------+------------+----------+
>
> So in the first insert the RecordID, InitOn (Using
> select NOW()), and LenChoice would be inserted.
> Looking something like this:
>
> +-----------------+-----------+------------+----------+
> | RecordID | InitOn | LenChoice | EndDate
> |
> +-----------------+-----------+------------+----------+
> | 10043 | 11/26/2004| 7 |
> |
> +-----------------+-----------+------------+----------+
>
> Now I try to use (and I've tried an update statement
> as well:
>
> Insert MyTable (EndDate) Values(DATE_ADD(InitOn,
> INTERVAL LenChoice DAY))
>
> Which I would hope to result in:
>
> +-----------------+-----------+------------+----------+
> | RecordID | InitOn | LenChoice | EndDate
> |
> +-----------------+-----------+------------+----------+
> | 10043 | 11/26/2004| 7
> |12/02/2004|
> +-----------------+-----------+------------+----------+
>
> However what is returned is an error message "Column
> EndDate cannot be NULL.
>
> Anyway idea what I'm doing wrong ?
I think you need an UPDATE statement
UPDATE MyTable set EndDate=DATE_ADD(InitOn, INTERVAL LenChoice DAY) WHERE
RecordID = <value> ;
Insert *always* creates new records if successful and cannot be used to
modify them.
Update *always* updates recirds in position and cannot be used to insert
them
Replace is a hybrid whcih can do either if you set your indexes right.
I think what you want is an Update, not an Insert.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
