Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pizzojm
Starting Member
20 Posts |
 Posted - 2003-03-12 : 15:46:07
|
| I have an update query question... I have a txt file that is being loaded into a temp table. I need to update certain rows in an existing table from this temp table. I have some ideas but want to make sure I am going to watch the exact rows. Suggestions would be great. Here is the tbl info...temp-----e_id (int unique)e_code (char)thetable-----e_id (int unique)e_code (char)bunch of other values that don't matter.I need to update the e_code in thetable to the value in the temp table where the two e_ids join.Thank You! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-12 : 16:09:01
|
| UPDATE thetableSET t.e_code = te.e_codeFROM thetable t, temp teWHERE t.e_id = te.e_idTara |
 |
|
|
pizzojm
Starting Member
20 Posts |
Posted - 2003-03-12 : 16:11:21
|
| Can you use a 'FROM' clause in UPDATES? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-12 : 16:18:08
|
| Yes, have a look at SQL Server Books Online for the syntax. Here is an example taken directly from Books Online:UPDATE titles SET t.ytd_sales = t.ytd_sales + s.qty FROM titles t, sales s WHERE t.title_id = s.title_id AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)Tara |
|
|
|
tiamiyu
Starting Member
10 Posts |
Posted - 2003-03-12 : 16:20:00
|
| Yes. You can use from in update statement if you are working with T-SQL. This is not true in Oracle though! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-12 : 16:35:41
|
It's not ANSI SQL either, it's a T-SQL extension (a damn handy one, if you ask me!)  The workaround for this is to create a view that joins the two tables together. As long as the database product can update that view correctly, then you'll get the same effect. |
|
|
|
pizzojm
Starting Member
20 Posts |
Posted - 2003-03-13 : 15:06:17
|
| fyi for those of us not lucky enough to always have T-SQL access...update updtbl set e_code = (select e_code from temptbl where temptbl.e_id = updtbl.e_id)where updtbl.e_id = (select e_id from temptbl where temptbl.e_id = updtbl.e_id);or something along those lines! Thanks... |
|
|
|
|
|
|
Update Question