0

Just demo. I have two table

Table a (id, name)
--id---name----
  1    John
  2    Jack
  3    Maria
  4    Bill
Table b (id, empid, datewrk)
--id---empid----datewrk----
  1      1    2012-12-12
  2      2    2012-12-14
  3      3    2012-12-16
  4      4    2012-12-17

I want update all name = null in table a where date in table b <= '2012-12-14', the result is

--id---name--
  1   NULL
  2   NULL

I have try code bellow but no work (only work with SELECT statement). I try in MySql Workbench and SQL Server 2012

UPDATE a
SET name = NUll
WHERE id IN (SELECT a.id FROM a 
JOIN b ON a.id = b.empid 
WHERE b.datewrk <= '2012-12-14');

Thank.

QHu91_IT
  • 189
  • 2
  • 12

2 Answers2

0

Your Update Statement should work, but in order to compare you have to convert '2012-12-14' to date.

UPDATE a
SET name = NULL
WHERE id IN (
   SELECT empid FROM b 
   WHERE datewrk <= STR_TO_DATE('2012-12-14', '%Y-%m-%d'));

Note that in your subquery you don't need table A.

Hope this helps.

agim
  • 1,841
  • 12
  • 19
  • Thank, your tip is very helpful. Try and work in sqlfiddle but nothing happen in mysql workbend, maybe problem in tool :D – QHu91_IT May 04 '14 at 20:39
  • Don't think there is a problem in tool. You simply have to remove the join from the subquery. It does not make sense to join a table in a subquery, which you want to update with the main query/statement. – agim May 06 '14 at 12:07
0

For mysql 

UPDATE a 
JOIN b ON a.id = b.empid 
SET a.name = NUll
WHERE b.datewrk <= '2012-12-14';

You don't need a subquery just join your table put set clause in right place then where clause

Fiddle Demo

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118