SQL Update Query

Question

I have 40,000 rows in my database that I need to update. However my select query that I have used is across two tables.

Example of my select query below:

select t1.*, t2.data, t2.more_data, t2.more_data2 
from table1 as t1, table2 as t2
where 
DATE(t1.date) >= '2014-10-23'
and
t1.direction = 10
and
t1.date_read is NULL
and
t1.fk_client is NULL
and
t1.id=t2.id
and
t2.data = 'this is dummy text';

I've been looking through other peoples requests on this, but I just cannot seem to get my head round it.

EDIT:

What I am wanting to do is to update t1.date_read with 2015-03-17 09:00:00 where t2.data is equal to 'this is dummy test'

there is no `t3` table declared in query for your `t3.more_data2` — Alex, Mar 17 '15 at 14:11
I can see multiple problems with your query... Missing `.` in the `SELECT`, missing closing quotes on both your date and data fields, missing table references (t3), an unusual `DATE` cast of a column named date, use of a `JOIN` syntax that has been deprecated for over 20 years... But what exactly is your question here? — Siyual, Mar 17 '15 at 14:14
Sorry missed out the . and t3 was wrongly put in, should have been t2. What I am wanting to do is to update t1.date_read with 2015-03-17 09:00:00 where t2.data = 'this is dummy test' — Bromers, Mar 17 '15 at 14:22
seem that what you are looking for http://stackoverflow.com/questions/9588423/sql-server-inner-join-when-updating — Borik, Mar 17 '15 at 14:41

score 1 · Accepted Answer · answered Mar 17 '15 at 14:38

What I am wanting to do is to update t1.date_read with 2015-03-17 09:00:00 where t2.data is equal to 'this is dummy test'

Just stick a reference to the other table in the WHERE clause of your update like so:

UPDATE table1
SET date_read = '2015-03-17 09:00:00'
where table1.id IN (SELECT id from table1 as t1
                    INNER JOIN table2 as t2 ON t1.id=t2.id
                    WHERE t2.data = 'this is dummy test')

If you want to include the other parameters you typed out in your question, just add them at the end...

--[code from above]
AND
DATE(table1.date) >= '2014-10-23'
AND
table1.direction = 10
AND
table1.date_read is NULL
AND
table1.fk_client is NULL

score 0 · Answer 2 · answered Mar 17 '15 at 14:16

First of all you have some errors in your query

t1* missing dot -> t1.*
t3.more_data2 - t3 is not decared in query
t2.data = 'this is dummy text; missed close quote
unclear join tables key

So try this corrected query and try to explain your goals:

SELECT
   t1.*,
   t2.data, 
   t2.more_data
FROM
   table1 as t1, 
INNER JOIN
   (SELECT *
    FROM   table2 
    WHERE
      data = 'this is dummy text'
   ) as t2
ON 
   t1.id=t2.id
WHERE 
   DATE(t1.date) >= '2014-10-23
   AND
   t1.direction = 10
   AND
   t1.date_read is NULL
   AND
   t1.fk_client is NULL

SQL Update Query

2 Answers2