CODEWITHSUNDEEP
mysqlwordpress
Ismael Moral
Ismael Moral

Reputation: 732

Update using two tables

I need to update one date from a mysql table, using value from another table .

I'm trying to do execute this on my mysql server,

Update

update wp_posts post 
  join wp_postmeta meta 
    on post.ID = meta.post_id 
   set post.post_date = meta.false_date 
 where meta.false_date <> '';

But I get the following error on terminal

Error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from wp_posts post, wp_postmeta meta where post.ID = meta.post_id and meta.false' at line 1

Meta

|    7647 |    1483 | false_date                    | 2016-12-30

SQL Sentence

update wp_posts post 
  join wp_postmeta meta 
    on post.ID = meta.post_id 
   set post.post_date = meta.false_date 
 where meta.false_date <> '';

Error 2

ERROR 1054 (42S22): Unknown column 'meta.false_date' in 'where clause'

What am I doing wrong?

Any help would be appreciate,

Jaster.

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

The correct syntax looks like this:

update post p join
       meta m
       on p.id = m.post_id
    set p.post_date = m.false_date 
    where m.false_date <> '';

Notes:

  • update in MySQL does not support a FROM clause.
  • You should be using proper, explicit JOIN syntax.
  • Table aliases make the query easier to write and to read.
  • Use single quotes for string and date constants -- and nothing else.
  • The standard SQL operator for inequality is <>.
  • If false_date is properly stored as a date, then your comparison should be meaningless. Perhaps you just intend: where false_date is not null.

Upvotes: 3

Related Questions