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 |
|
MikeB
Constraint Violating Yak Guru
387 Posts |
 Posted - 2003-10-27 : 16:17:08
|
| Is it possible to update a recordset created from the UNION of two tables if the field names are the same?UPDATE * SET field = 0WHERE Barcode IN SELECT Barcode FROM tblOne WHERE field = 'something' UNION SELECT Barcode FROM tblTwo WHERE field = 'Something'If I enter this, it says there is a syntax error in the UPDATE statement at (*).Any ideas, is this even possible?Mike B |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-10-27 : 16:29:01
|
| I'm pretty sure you have to have just a single table in the UPDATE ____ portion of the command.--------------------------------------------------------Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url] |
 |
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2003-10-27 : 16:29:26
|
| I tried the following, but for some reason it makes me nervous. UPDATE tblOne, tblTwo SET tblOne.Load = 0, tblTwo.Load = 0 WHERE ((tblOne.Project = '10002' AND tblOne.ShippingDate = #10/23/03# AND tblOne.Load = 1) OR (tblTwo.Project = '10002' AND tblTwo.ShippingDate = #10/23/03# AND tblTwo.Load = 1))This seems to work. It does however report the wrong number of records being updated. When I execute this update statement it says 2 records are about to be updated, however 3 records are updated. 1 record from tblOne and 2 records from tbl2.Any thoughts on this?Mike B.P.S. I read on MSDN that a union query cannot be directly updated :(. |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2003-10-27 : 16:38:27
|
| P.S.S., That update statement in my last post will only update if both tables have a record matching the criteria specified! Looks like I will have to stick to Updating one table then the other.Mike B |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-10-28 : 05:22:25
|
| LOL, Mike,I have never used multi-tables updating in one query. Just becauseI can't even think up any need for it. Plus, it seems 'dangerous' thing.As to your sample, I think it goes like this:the message "2 rows being updated" referred to the query's rowset as whole,namely, to the cartesian product of the 2 tables. And in this product you haveonly 2 rows matching all WHERE clause criterii... but these 2 rows were 'made'of 3 (1 + 2) rows from the underlying tables. |
|
|
|
|
|
|
|
|
Update a UNION recordset?