1

Here's what needs to be done: Match 2 fields from different tables in SQL Server( done ), update fields in table1 where table1.field1 = table2.field2 (problem, it just updates all the records in the table)

Here's what I have , where tempTableName is a table imported to SQL Server for the purpose of the query:

"UPDATE Table1 SET Table1.fieldN ='" + DateTime.Now.DayOfYear + "' FROM " + tempTableName + " CROSS JOIN Table1 WHERE (" + tempTableName + ".fieldX = Table1.fieldY)"

Here's what I've figured out:

everything after the FROM is useless as far as actual functionality is concerned, it runs the query but the results are not "linked" in any way to the actual UPDATE statement

To sum up :

The query I've figured out updates all records in table1, I need the query to update only the rows matched by the query after FROM

PS. Forgive me if this seems trivial and that I haven't done research but the fact of the matter is that 2 weeks ago I'd never even heard of SQL and have relied heavily on SO for direction and advice.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
Dani
  • 2,480
  • 3
  • 21
  • 27

1 Answers1

2

Your CROSS JOIN should be an INNER JOIN instead.

"UPDATE t1
    SET fieldN ='" + DateTime.Now.DayOfYear + "' 
    FROM " + tempTableName + " t2 INNER JOIN Table1 t1 ON t1.fieldy = t2.fieldx"
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • thanks again, whats up with this though ? [Inner Join vs Cross Join](http://stackoverflow.com/questions/670980/performance-of-inner-join-compared-to-cross-join) – Dani Aug 02 '11 at 21:34
  • 1
    The example you cite is for a `SELECT` statement. I don't believe that behavior translates to `UPDATE` statements. In any case, explicit `INNER JOIN` is the preferable way to go for either case. – Joe Stefanelli Aug 02 '11 at 21:43