Can I use SQL to update multiple rows with multiple values from another table?

Posted by stu on Stack Overflow See other posts from Stack Overflow or by stu
Published on 2010-03-08T21:50:39Z Indexed on 2010/03/08 21:51 UTC
Read the original article Hit count: 252

Filed under:

sql

|

tips-and-tricks

I've got a one to many table with ids and traits for those users. I want to copy one user's settings to another.

userid    trait    onoff
------    -----    -----
1          1         on
1          2         on
1          3         on
1          4         on
2          1         off
2          2         off
2          3         off
2          4         off

So I want to copy the onoff values from user 1 to 2, so that user2 has everything on when I'm done.

update table set onoff = (select onoff from table where userid=1) where userid=2

that's the basic idea, but obviously doesn't work. I imagine I want the server to think in terms of a correlated subquery, but the outer part isn't a query, it's an update.

Is this even possible in one step?

© Stack Overflow or respective owner

Related posts about sql

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More
SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More
Error with SQL Server Setup 2012 on Windows 2012

as seen on Server Fault - Search for 'Server Fault'
I am trying to install SQL Server on Windows 2012. I was able to finally get the wizard up and running after making some changes on the server, but now it fails no matter what I do with the following error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: … >>> More
How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More
Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder… >>> More

Related posts about tips-and-tricks

More Linux Tips and Tricks

as seen on Internet.com - Search for 'Internet.com'
we learn how easy it is to configure OpenSSH to use secure public key authentication instead of a password login. >>> More
Tutorial: Automated Linux Server Backup Tips and Tricks

as seen on Internet.com - Search for 'Internet.com'
This tutorial walks us step-by-step through creating a custom server backup script with some nifty features like bandwith throttling to avoid bogging down the server being backed up, how to exclude files, and how to synchronize deleted files. >>> More
Lua Patterns,Tips and Tricks

as seen on Stack Overflow - Search for 'Stack Overflow'
This is a Tips & Tricks question with the purpose of letting people accumulate their patterns, tips and tricks for Lua. Lua is a great scripting language, however there is a lack of documented patterns, and I'm sure everyone has their favorites, so newcomers and people wondering if they should… >>> More
SQL SERVER – Fix : Error 3623 – An invalid floating point operation occurred

as seen on SQL Authority - Search for 'SQL Authority'
Going back in time, I always had a problem with mathematics. It was a great subject and I loved it a lot but I only mastered it after practices a lot. I learned that mathematics problems should be addressed systematically and being verbose is not a trick, I learned to solve any problem. Recently one… >>> More
SQL SERVER – Introduction to Extended Events – Finding Long Running Queries

as seen on SQL Authority - Search for 'SQL Authority'
The job of an SQL Consultant is very interesting as always. The month before, I was busy doing query optimization and performance tuning projects for our clients, and this month, I am busy delivering my performance in Microsoft SQL Server 2005/2008 Query Optimization and & Performance Tuning Course… >>> More