0

I'm trying to do an UPDATE from a SELECT following the format given by this SO thread

It's not working. I'm wondering if it's because I need a different UPDATE...SELECT structure when performing calculations.

I've tried various versions of the following:

        UPDATE tbl_usertime uat
            SET uat.delay_qual = (TIMESTAMPDIFF(MINUTE, uat.view_date, '2011-10-11 15:22:25') > adt.delay), 
            freq_qual = (adt.frequency > uat.imps_today)
        FROM tbl_timing adt
        JOIN tbl_usertime uat 
            ON uat.ad_id = adt.ad_id;

...but it returns 'error in SQL syntax...near 'FROM tbl_timing adt JOIN tbl_usertime uat'

Any ideas? Thanks!

Community
  • 1
  • 1
Kyle Cureau
  • 19,028
  • 23
  • 75
  • 104
  • `FROM` is not valid in update queries, only `select` ones. – Marc B Oct 11 '11 at 21:05
  • 1
    @MarcB He's copying from a Sql Server thread and this is valid Sql Server syntax. – Icarus Oct 11 '11 at 21:07
  • @Icarus: Yes, if the update query contains a select sub-query. OP's code snippet doesn't. `update` queries do not support `from` in any way shape or form, as it's not part of the SQL language spec. – Marc B Oct 11 '11 at 21:08
  • 1
    @MarcB yes they do in SQL Server. Check the answer in the linked post. This doesn't work for the OP because ofcourse the linked post is for SQL Server and he intended to copy/paste in MySQL. Not sure if you can do it, but obviously the syntax is not the same. – AJC Oct 11 '11 at 21:18

2 Answers2

2

The MySQL syntax is a little bit different, try this:

update tbl_usertime uat join tbl_timing adt on uat.ad_id=adt.ad_id set
    uat.delay_qual = (TIMESTAMPDIFF(MINUTE, uat.view_date, '2011-10-11 15:22:25') > adt.delay), 
        freq_qual = (adt.frequency > uat.imps_today)
Xint0
  • 5,221
  • 2
  • 27
  • 29
1

I am not sure it will work, but reading MySQL documentation gives me the impression that may be valid:

UPDATE tbl_usertime join tbl_timing
SET tbl_usertime.delay_qual = (TIMESTAMPDIFF(MINUTE, tbl_usertime.view_date, '2011-10-11 15:22:25') > tbl_timing.delay), 
    freq_qual = (tbl_timing.frequency > tbl_usertime.imps_today)
where 
tbl_usertime.ad_id = tbl_timing.ad_id;
Icarus
  • 63,293
  • 14
  • 100
  • 115