Update row in Table A from row in Table B

Hello,

I have 2 tables.... table A and table B.
Ok, In a stored procedure, I pass in an int value @ID
I want to get the row from Table B WHERE ID =@ID....and then UPDATE the Row in Table A that has the same ID...with the values from table B.
Table A

ID | Col1 | Col2
-----------------------
2 a a

Table B

ID | Col1 | Col2
-----------------------
2 b b


I would like to update A.Col1 with B.Col1 and A.Col2 with B.Col2
Can someone help me out with this syntax?

Thanks a lot.
0
bremmington
9/5/2004 10:46:30 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
1168 Views

Similar Articles

[PageSpeed] 51

update tableA

set col1 = b.col1, col2 = b.col2
from tablea a
join tableb b
on a.id = b.id

You can't use the 'a.' prefix on the table you're updating until after the 'FROM'. That seems to hang people up when you want to do this: "set a.col1 = "... That will error out
0
pdraigh
9/7/2004 2:50:06 PM
Thank you very much.  This works great.
0
bremmington
10/22/2004 11:34:23 AM
Reply:

Similar Artilces:

Update a rows in one table for each row in other table
Hello all, i am a newbie in SQL and i want to ask for your help in order to do the following update statement. I have a table tblUsers which holds all the users, one of the fields in their GroupId. In the UserGroup table i have a field "UsersCount" and in that field i want to keep the number of the users in that group. Basically i want everytime i am saving a user to recalculate the users in all usergroups. I am trying to have something like:SELECT User_GroupId, count(User_GroupId) as UsersCount FROM tblUsersWHERE DeletedFlag = 0 GROUP BY User_GroupId and then:"FOR EACH" User_GroupIdUPDAT...

Stored Procedure that fetch each row of a table and update rows in another table
I am working with the following two tables: Category(NewID,OldID) Link(CategoryID,BusinessID) All fields are of Integer Type. I need to write a stored procedure in sql 2000 which works as follows: Select all the NewID and OldID from the Category Table (SELECT NewID,OldID FROM Category) Then for each rows fetched from last query, execute a update query in the Link table. For Example, Let @NID be the NewID for each rows and @OID be the OldID for each rows. Then the query for each row should be.. UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID Please help me ...

SQL Old school *= Question select All rows from table A and rows from B
Im using SQL 2005. Love it.In the old TSQL world could useselect current.user, weekly.user, weekly.hoursfrom current, weeklywhere current.user *= Weekly.user and get all the current users from current table AND users from weekly table that have weekly hours. Somehitng like dan  12fred 24sue nullwhere sue is in the current table, but not in the weekly table.I've tried outside joins like this SELECT     dbo.ViewCurrentUser.UserName, dbo.PS_HR_Hrs.DateWorkedFROM         dbo.ViewCurrentUser LEFT OUTER JOIN&n...

SQL call to count the total rows in Table B for each user in Table A
I have 2 tables:   TableA: Name UserA UserB UserC   Table B: Name               Data UserA              xxx UserB              asdasd UserB              ewrsad UserC              dsafasc UserA        &nbs...

Is having a trigger that inserts a row in Table 'A', when a row in same table is inserted by ADo.Net code?
I want to insert a row for a Global user  in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a s...

Add row to table with table.row.add()
Everytime I click on a button I want to add a tableRow to a table like this:        TextBox TB = new TextBox();                TableCell Cell = new TableCell();        Cell.Controls.Add(TB);        TableRow Row = new TableRow();        Row.Cells.Add(Cell);        table.Rows.Add(Row);It works the first time, but when I click the button again the last r...

Showing table columns from multiple tables in Formview, but insert/update/delete rows of Master table
I have a page on which I have GridView and FomView control. On selection of a row in GridView the row gets displayed in the Item template of the FormVIew. The values are the result of inner join from multiple tables i.e. table Stock,Product and Workshop.  Please let me know how I could insert/update/delete data for the base table i.e. Stock table, but be able to see the column values from the associated tables on the Itemtemplate,Edittemplate and Inserttemplate of the FormView. Thanks,Anita Hi:   Take a look at this tutorial. It's for DetailsView and same ...

Adding table rows to a table in an update panel
I'm trying to add table rows to a table that has already been added to the update panel (ie, in design view). When I run my code, the text changes on the page, but I dont get additional rows, its as if its forgetting about the first row that was already added and simply over writing it... Anyone got any thoughts?  I had a label in there before where I'd reference the label and add text to the existing text, that worked fine, the text that was already there just stayed and the dialogue got longer etc, why is it different with the table rows? Any help would be appreciated..Rega...

Select rows from a table based on multiple rows of another table
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

Deleting rows from one table based upon rows in another table
I need to delete rows from table1 based on rows in table2. table1 has 1.5 million rows from which I need to delete 20,000 existing in table2. The table structures are the same and I must exactly match 10 (of the 15) columns in the tables. I also need to limit the number of rows being worked on, in a single transaction, to 500 or so. Hi Mike, try the following: set rowcount 500 DELETE table1 from table1 t1,table2 t2 where t1.col1=t2.col1 and t1.col2=t2.col2 and t1.col3=t2.col3 and ........ t1.col10=t2.col10 HTH. Regards P:S You must re-execute the DML statement above 40 time...

Select rows from a table based on multiple rows of another table #4
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

Select rows from a table based on multiple rows of another table #3
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

Select rows from a table based on multiple rows of another table #2
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. Sorry technical problems! I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. I have a table which holds all of my project info and I have another table which holds discriptive project codes like project type, project category, progect life cycle.So in the pro...

adding html table columns from SQL table rows
I want to put data from an SQL table into an HTML table, but I want to start a new row in the HTML table when it is 5 columns wide. I have done this in classic ASP with the following code: TABLE NAME How can I accomplish the same thing with asp.net? I have created my SqlDataSource and the SelectCommand no problem, but I'm kind of stuck how to loop through the recordset. Why do you want to do it manally? Data binding is for such purposes only. Fill a DataSet or DataTable with the SqlCommand and do: GridView.DataSource = DataSet.Ta...

Web resources about - Update row in Table A from row in Table B - asp.net.sql-datasource

Windows Update - Wikipedia, the free encyclopedia
Windows Update is a service provided by Microsoft that provides updates for the Microsoft Windows operating system and its installed components, ...

Gizmodo.com Updates
Nobody should have to use a pay phone , natural disaster or no natural disaster. So if your gadgets are out of juice after Sandy, here’s a tiny ...

Lifehacker.com Updates
By Whitson Gordon Ever run into a situation where you have multiple wireless networks available, but one is clearly better than the other? WiFi ...

Facebook To Update Mobile Apps Every 4 To 8 Weeks
On the same day that Facebook released a bunch of mobile updates for Apple and Android devices , the social network also announced that it will ...

Microsoft Updates Bing Logo as Search Service Grows Up
Microsoft on Thursday plans to introduce a new version of the logo for Bing, the No. 2 search engine, switching to green from yello and putting ...

Android launcher update adds auto-rotate, forces icon size consistency
Icons now get forced to a standard size. An update to the Google Now Launcher has brought some nifty new features to Android's home screen. ...

Did the core algorithm update deal with freshness?
Google rolled out a huge algorithm update over the weekend but so far there has been no confirmation of what it actually did. Publishing sites ...

Nissan & Renault CEO & Chairman Talks Electric Cars, Autonomous Driving, & OTA Updates
... a Ghosn fan. I love the direct, honest way [&hellip Nissan & Renault CEO & Chairman Talks Electric Cars, Autonomous Driving, & OTA Updates ...

IDG Contributor Network: 6 critical updates for January Patch Tuesday
Microsoft has started the year with a truly unusual Patch Tuesday. There are nine updates for January, with six rated as critical and the remaining ...

Basic Income Update
Regular readers of Continuations know that I have been writing a fair bit about Basic Income and have also given several talks including Techonomy ...

Resources last updated: 1/15/2016 7:20:56 AM