UPDATE SQL Syntax problem

[LittleSmudge]

I need to do an Update that involves writing to table tblDateLog based on the values in tblConcess as follows

UPDATE tblDateLog, tblConcess 
SET tblDateLog.OpenEarly = [EndDate] + [DefOpenEarly], 
tblDateLog.DataDueIn = [EndDate] + [DefDataIn], 
tblDateLog.FirstRemind = [EndDate] + [DefFirstRemind], 
tblDateLog.SecondRemind = [EndDate] + [DefSecondRemind], 
tblDateLog.WarningFlag = [EndDate] + [DefWarningFlag] 
WHERE tblConcess.ConcesId = tblDateLog.ConcesRef 
AND DataDueIn = 0000-00-00

field names in [braces] are all from tblConcess

Now according to the MySQL manual this should work exactly as typed in v 4.0.4 and onwards

But wouldn't you know it - I'm stuck with version 3.23.58


Other than changing ISP - any ideas on how I can perform the function I need ?



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[TonyGroves]

If it can't be done using program code, then how about something like:
[tt]
CREATE TABLE t AS
SELECT
d.*,
c.ConcesId ci,
c.EndDate+c.DefOpenEarly oe,
c.EndDate+c.DefDataIn di,
c.EndDate+c.DefFirstRemind fr,
c.EndDate+c.DefSecondRemind sr,
c.EndDate+c.DefWarningFlag wf
FROM
tblDateLog d LEFT JOIN tblConcess c
ON d.ConcesRef=c.ConcesId;

UPDATE t
SET
OpenEarly=oe,
DataDueIn=di,
FirstRemind=fr,
SecondRemind=sr,
WarningFlag=wf
WHERE ci IS NOT NULL and DataDueIn=0;

DROP TABLE tblDateLog;

ALTER TABLE t
DROP ci,
DROP oe,
DROP di,
DROP fr,
DROP sr,
DROP wf,
RENAME tblDateLog,
ADD PRIMARY KEY ... ,
ADD INDEX ... ;
[/tt]
I know - it's awkward, slow, (and probably wrong) but if you're desperate ...

 

[LittleSmudge]

Oh Tony - I'm desperate !

I'll give that lot a try.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

Sorry Tony - but that doesn't do what I need.

tblDateLog has lots of records.
Only a few of which I need to work on ( the ones with DateDueIn = 0 )

Your approach updates the rows I need to work on but I end up losing all of the other rows.

I've fiddled around with your general approach to look for some other options but I still end up needing two tables and a From clause in the UPDATE statement.


Any other ideas ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[TonyGroves]

I'm afraid my thinking cap doesn't really fit any more, but a couple of other approaches worth considering might be:

(1) Write a program to do it.
(2) Install the latest MySQL version on your own computer, download the table files, run your original update query, then copy the files back to your ISP's server.

 

[LittleSmudge]

PARTIAL SUCCESS

SELECT
  d.*,
  @ci:=c.ConcesId,
  @oe:=d.EndDate+c.DefOpenEarly,
  @di:=d.EndDate+c.DefDataIn,
  @fr:=d.EndDate+c.DefFirstRemind,
  @sr:=d.EndDate+c.DefFirstRemind+c.DefSecondRemind,
  @wf:=d.EndDate+c.DefFirstRemind+c.DefSecondRemind+c.DefWarningFlag
FROM
  tblDateLog d LEFT JOIN tblConces c
  ON d.ConcesRef=c.ConcesId
WHERE ConcesId = 2;

UPDATE tblDateLog 
SET tblDateLog.OpenEarly = @oe, 
tblDateLog.DataDueIn = @di, 
tblDateLog.FirstRemind = @fr, 
tblDateLog.SecondRemind = @sr, 
tblDateLog.WarningFlag = @wf 
WHERE tblDateLog.ConcesRef = @ci
AND tblDateLog.DataDueIn = 0 ;

Using User Variables and working one record at a time from the tblConces I get it to work.

EXCEPT
EndDate is the last day of the month and when OpenEarly, DataIn are negatrive integers it woorks fine.
However, when FirstRemind or SecondRemind or WarningFlag are positive then I get a 000-00-00 date out.

Obviously adding days to a date in MySQL doesn't cope with going round the corner at month ends.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

FINALLY

SELECT
  d.*,
  @ci:=c.ConcesId,
  @oe:=Date_Add(d.EndDate, INTERVAL c.DefOpenEarly DAY),
  @di:=Date_Add(d.EndDate, INTERVAL c.DefDataIn DAY ),
  @fr:=Date_Add(d.EndDate, INTERVAL c.DefFirstRemind DAY),
  @sr:=Date_Add(d.EndDate, INTERVAL c.DefFirstRemind+c.DefSecondRemind DAY),
  @wf:=Date_Add(d.EndDate, INTERVAL c.DefFirstRemind+c.DefSecondRemind+c.DefWarningFlag DAY)
FROM
  tblDateLog d LEFT JOIN tblConces c
  ON d.ConcesRef=c.ConcesId
WHERE ConcesId = 1;

UPDATE tblDateLog 
SET tblDateLog.OpenEarly = @oe, 
tblDateLog.DataDueIn = @di, 
tblDateLog.FirstRemind = @fr, 
tblDateLog.SecondRemind = @sr, 
tblDateLog.WarningFlag = @wf 
WHERE tblDateLog.ConcesRef = @ci
AND tblDateLog.DataDueIn = 0 ;

Does the job.

BUT still only one record at a time.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.