473,695 Members | 1,989 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

updating multiple fields

How can I update each record in a table, based on a value in another table
with a single SQL statement?

For example, suppose I have the following two tables:

Table1

Name Something Color
-----------------------------------------
John GHAS Blue
John DDSS Blue
John EESS Blue
Paul xxxx Red
Ringo HJKS Red
Ringo FFFS Red
Sara hjkd Purple
Sara TTHE Purple
Jimi sdkjls Green
Table2

Name Color
------------------------
John ?
Paul ?
Ringo ?
Sara ?
Jimi ?
How can I update the color field in table 2 to correspond with the color
field in table1 (so I can normalize the db and delete the color field from
table1)?

I know I could open table2 and loop through within my app; just wondering
about a single SQL statement that would do it. I need a similar technique in
other places as part of my app.

Thanks,

Calan
Sep 22 '05 #1
5 7823
SQL
Update t2 set color = t1.color
from Table1 t1
join Table2 t2 on t1.name =t2.name

http://sqlservercode.blogspot.com/

Sep 22 '05 #2
On Thu, 22 Sep 2005 19:39:37 GMT, calan wrote:
How can I update each record in a table, based on a value in another table
with a single SQL statement?

For example, suppose I have the following two tables: (snip)Table1

Name Something Color
-----------------------------------------
John GHAS Blue
John DDSS Blue
John EESS Blue
Paul xxxx Red
Ringo HJKS Red
Ringo FFFS Red
Sara hjkd Purple
Sara TTHE Purple
Jimi sdkjls Green
Table2

Name Color
------------------------
John ?
Paul ?
Ringo ?
Sara ?
Jimi ?
How can I update the color field in table 2 to correspond with the color
field in table1 (so I can normalize the db and delete the color field from
table1)?


Hi Calan,

The code suggested by "SQL" will work, but it won't warn you if there
are names with more than one associated color in Table1. Intead, it'll
just pick one of the colors, using an unpredictable algorithm.

Here's a code that will throw an error if there is more than one
matching color:

UPDATE Table2
SET Color = (SELECT DISTINCT Color
FROM Table1
WHERE Table1.Name = Table2.Name)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Sep 22 '05 #3
Generally when somebody asks questions in the groups it would be better
if they could give like this...

--This is a suggestion given by one database expert not me.

create table Color_Master
(
Name char(5) NOT NULL,
Something varchar(10) NULL,
Color varchar(10) NOT NULL,
)
create table Color_Transacti on
(
Name char(5) NOT NULL,
Color varchar(10) NOT NULL default '' ,
)
go
insert into Color_Master values ('John','ghas', 'blue')
insert into Color_Master values ('John','ghas', 'red')
insert into Color_Master values ('kumar','somet hing','orange')
insert into Color_Transacti on(Name) values ('John')
insert into Color_Transacti on(Name) values ('kumar')
update Color_Transacti on
set Color_Transacti on.Color = Color_Master.co lor
from Color_Master
where Color_Transacti on.Name = Color_Master.Na me

-- OUTPUT

-- Name Color
----- ----------
-- John red
-- kumar orange
UPDATE Color_Transacti on
SET Color_Transacti on.Color = (SELECT DISTINCT Color_Master.co lor
FROM Color_Master
WHERE Color_Transacti on.Name = Color_Master.Na me)

--it is throwing an error
--Server: Msg 512, Level 16, State 1, Line 1
--Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
--The statement has been terminated.

--What I think is if the same names have different values it will be
very hard to distinguish between the same names
--with different colors.correct me if i am wrong

Hugo Kornelis wrote:
On Thu, 22 Sep 2005 19:39:37 GMT, calan wrote:
How can I update each record in a table, based on a value in another table
with a single SQL statement?

For example, suppose I have the following two tables:

(snip)
Table1

Name Something Color
-----------------------------------------
John GHAS Blue
John DDSS Blue
John EESS Blue
Paul xxxx Red
Ringo HJKS Red
Ringo FFFS Red
Sara hjkd Purple
Sara TTHE Purple
Jimi sdkjls Green
Table2

Name Color
------------------------
John ?
Paul ?
Ringo ?
Sara ?
Jimi ?
How can I update the color field in table 2 to correspond with the color
field in table1 (so I can normalize the db and delete the color field from
table1)?


Hi Calan,

The code suggested by "SQL" will work, but it won't warn you if there
are names with more than one associated color in Table1. Intead, it'll
just pick one of the colors, using an unpredictable algorithm.

Here's a code that will throw an error if there is more than one
matching color:

UPDATE Table2
SET Color = (SELECT DISTINCT Color
FROM Table1
WHERE Table1.Name = Table2.Name)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)


Sep 23 '05 #4
Yes. The real question is in the requirement not in the solution: What
do you want to happen if a single name has more than one colour? The
first example UPDATE has a dubious bug/feature that says "I don't care
- pick one of those colours at random". In the second case the answer
is "I do care - warn me about it so that I can fix the problem
properly".

--
David Portas
SQL Server MVP
--

Sep 23 '05 #5
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

CREATE TABLE Foobar
(user_name CHAR(15) NOT NULL,
something VARCHAR(10) NOT NULL,
color_code INTEGER DEFAULT 0 NOT NULL
REFERENCES Colors(color_co de));

Are you using Land color numbers? PanTone? Another industry standard?
CREATE TABLE Colors
(color_code INTEGER NOT NULL,
color_name VARCHAR(10) NOT NULL);
INSERT INTO Colors VALUES (0, 'Mixed');

But ignoring that, I think what you want is:

CREATE TABLE FavoriteColors
(user_name CHAR(15) NOT NULL,
color_code INTEGER DEFAULT 0 NOT NULL
REFERENCES Colors(color_co de));

INSERT INTO FavoriteColors
SELECT user_name,
CASE WHEN MIN(color_code) = MAX(color_code)
THEN MIN(color_code) ELSE 0 END;
FROM Foobar
GROUP BY user_name;

If your data is dirty and someone has more than one color, this will
give them a special code. You would, of course, never use the
proprietary, unpredictable UPDATE.. FROM syntax.

Sep 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
16193
by: Jason | last post by:
Let's say I have an html form with 20 or 30 fields in it. The form submits the fields via POST to a php page which updates a table in a database with the $_POST vars. Which makes more sense? 1) simply UPDATING the values for all fields in the table, whether or not any particular field has actually changed 2) running a second SELECT statement and comparing the $_POST vars to the returned values, and only UPDATING those that have...
1
2048
by: Roy Adams | last post by:
Hi everyone I'm trying to build a shopping cart app using a db the part I'm stuck on is the fact that, if someone adds a product that they have previously added to the cart. I've got it set up to check whether the size and colour fields match what's in the db, if they do then you've already added this item with the same colours and size so, only update the quantity field, if they don't match, then insert a new record because the size and...
5
2636
by: Richard Stanton | last post by:
Hello all My database has a main form linked to table1. It has several subforms on the main form, all linked to table2. Table1 and Table2 are linked by primary/foreign key, no duplicates allowed, one-to-one. If I update field(s) on a single subform it works fine but when I update fields on multiple subforms ie without closing the form between updates, I get the following error:
3
13061
by: Tc | last post by:
Hi, I was curious, I am thinking of writing an application that loads a dataset from a database that resides on a server. The question I have is this, if multiple copies of the app will be running at once will there be problems with data updates? The reason I ask is I'm thinking like this: User1 launches the app and the dataset is created from the data in the DB.
34
10834
by: Jeff | last post by:
For years I have been using VBA extensively for updating data to tables after processing. By this I mean if I had to do some intensive processing that resulted in data in temp tables, I would have VBA code that wrote the results of that away to the db, either creating new records or updating existing records, whichever was relevant. This may also include deleting records. Now I generally do this by opening a recordset on the source data...
8
4612
by: Chris A via AccessMonster.com | last post by:
I have an interesting problem that I have yet to come accross that I can't change data structure on because it is an export from filemaker I am reformatting for another dept. anyway. I have a table like so... Table 1 Field1 Field2 Field3 E1 April 2006 AA, BB, CC E2 April 2006 AA, BB, CC,DD, EE E3 April 2006 AA, BB
9
4989
by: Marianne160 | last post by:
Hi, I know there are various answers to this problem available on the web but none of them seem to work for me. I am using Access 2003 to make a form to look up data from a table. I have so far created three combo boxes that cascade down from Name to Project to Date and when this has been chosen I have four textbox fields I want to display based on this selection. I tried linking the last combo box comboDate to update these fields after...
18
1971
omerbutt
by: omerbutt | last post by:
AJAX PROB WITH MULTIPLE RECORDS helo iam having problem in ma code will any body look out an help, i am trying t add sale record in the database and the checkthe quantity of the part slod and decrement it to the current quantity for example if tehre is a part woth part no 10023 and its quantity in show room is 20 and total quantity is 25 then if there is a sale of 2 parts then i have to add the sale and decrement the current quantity in the...
0
2499
by: Mike | last post by:
So here's the situation (.NET 2.0 btw): I have a form, and on this form is a textbox among many other databound controls. The textbox is bound to a field in a data table via the Text property. In this table there are multiple columns that cannot be NULL, which, are bound to other controls (but they're not really important at this time). I create a new row via the currency manager like so: _currencyManager.AddNew() _currentRow =...
4
4887
by: MoroccoIT | last post by:
Greetings - I saw somewhat similar code (pls see link below) that does mupltiple files upload. It works fine, but I wanted to populate the database with the same files that are uploaded to mydirectory, but for some reason, I am getting different file names on the database. Here is the full code: please do serach on kewword "database" to see where I added my database code - that where I need help with. And here the link where I got it...
0
8587
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8867
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8841
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7688
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5847
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4351
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4599
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3025
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1987
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.