Updating multiple fields in sql
Booking Details WHERE My [email protected] ID UPDATE SHIPMENT SET Org Address1 = @Org Address1, Org Address2 = @Org Address2, Org City = @Org City, Org State = @Org State, Org Zip = @Org Zip, Dest Address1 = @Dest Address1, Dest Address2 = @Dest Address2, Dest City = @Dest City, Dest State = @Dest State, Dest Zip = @Dest Zip WHERE My [email protected] My ID2 UPDATE SHIPMENT SET Org Address1 = BD.
Field2, (list of fields...) FROM ( SELECT Field1, Field2, (list of fields...) FROM Profiler
I need to update several hundred visits with the same note and tried to do a test note on my demo db and when I executed the script, no rows were updated.
I know I could open table2 and loop through within my app; just wondering about a single SQL statement that would do it. How can I update the color field in table 2 to correspond with the colorfield in table1 (so I can normalize the db and delete the color field fromtable1)?
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)?
create table Color_Master ( Name char(5) NOT NULL, Something varchar(10) NULL, Color varchar(10) NOT NULL, ) create table Color_Transaction ( 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','something','orange') insert into Color_Transaction(Name) values ('John') insert into Color_Transaction(Name) values ('kumar') update Color_Transaction set Color_Transaction.
Color = Color_Master.color from Color_Master where Color_Transaction. Name -- OUTPUT -- Name Color ----- ---------- -- John red -- kumar orange UPDATE Color_Transaction SET Color_Transaction. How can I update the color field in table 2 to correspond with the colorfield in table1 (so I can normalize the db and delete the color field fromtable1)?
Please join our friendly community by clicking the button below - it only takes a few seconds and is totally free.
You'll be able to ask questions about MSSQL or chat with the community and help others.
=, = or when the subquery is used as an expression. --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: Table1Name 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 Table2Name Color------------------------John ? 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) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) 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?
Color = (SELECT DISTINCT Color_Master.color FROM Color_Master WHERE Color_Transaction. Name) --it is throwing an error --Server: Msg 512, Level 16, State 1, Line 1 --Subquery returned more than 1 value. 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.Tags: Adult Dating, affair dating, sex dating