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 |
jere1972
Starting Member
2 Posts |
Posted - 2014-02-24 : 13:31:19
|
Need some help on how to modify/change data that is in a SQL table column.Here is what I need or haveI have 2 tabels, Table 1 and Table 2, within those 2 tables there is 1 Column that has the same column heading aswell as data within,(I will call it the serial_number column) there is also a 2nd column that is labeled the Same in both tables, I would like to be able to update the Values in the 2nd column of table 1 with the values from table 2 column 3, using the Serial_number colume as my matching reference between the 2, the data is not in the same order between the 2 Tables, More or less I need to set the value of table 1/column 2 to match table 2/column 3, where the serial_numbers are the same in both tables serial_number columns, the data being changed is set as Small int...Any help anyone can provide would be extremely Appreciated, as my working knowledge of Sql Scripting is at a close 0 |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-02-24 : 15:09:19
|
Just JOIN the two tables on serial_number and do the update:something like this:update t2 set t2.col3 = t1.col2from table1 t1inner join table2 t2 on t2.serial_number = t1.serial_number EDIT:FYI - it is always a good idea to SELECT the data first just as a reality check so you can see what you are about to update.--update t2 set-- t2.col3 = t1.col2SELECT t1.serial_number, t2.col3, t1.col2from table1 t1inner join table2 t2 on t2.serial_number = t1.serial_number Another safety check is to first BEGIN TRANthen check the results - if you don't like it then: ROLLBACK TRANif you do like the results then: COMMIT TRANBe One with the OptimizerTG |
|
|
maggie21620
Starting Member
27 Posts |
Posted - 2014-02-24 : 16:15:16
|
can you help with this, not sure where to put the column or even if that is the right command --COLUMN PPVTotal FORMAT $99,990 SELECT adHock.dbo.PPVVINCE$.Corps, adHock.dbo.PPVVINCE$.House, adHock.dbo.PPVVINCE$.Cust, SUM(PPV_TOTAL) AS PPVTotal FROM adHock.dbo.PPVVINCE$ INNER JOIN IDST_74_CUSTOMER ON adHock.dbo.PPVVINCE$.Corps = IDST_74_CUSTOMER.ACCTCORP AND adHock.dbo.PPVVINCE$.House = IDST_74_CUSTOMER.HOUSE AND adHock.dbo.PPVVINCE$.Cust = IDST_74_CUSTOMER.CUST GROUP BY adHock.dbo.PPVVINCE$.Corps, adHock.dbo.PPVVINCE$.House, adHock.dbo.PPVVINCE$.CustORDER BY PPVTotanone |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-27 : 02:54:01
|
quote: Originally posted by maggie21620 can you help with this, not sure where to put the column or even if that is the right command --COLUMN PPVTotal FORMAT $99,990 SELECT adHock.dbo.PPVVINCE$.Corps, adHock.dbo.PPVVINCE$.House, adHock.dbo.PPVVINCE$.Cust, SUM(PPV_TOTAL) AS PPVTotal FROM adHock.dbo.PPVVINCE$ INNER JOIN IDST_74_CUSTOMER ON adHock.dbo.PPVVINCE$.Corps = IDST_74_CUSTOMER.ACCTCORP AND adHock.dbo.PPVVINCE$.House = IDST_74_CUSTOMER.HOUSE AND adHock.dbo.PPVVINCE$.Cust = IDST_74_CUSTOMER.CUST GROUP BY adHock.dbo.PPVVINCE$.Corps, adHock.dbo.PPVVINCE$.House, adHock.dbo.PPVVINCE$.CustORDER BY PPVTotanone
this looks different from your initial explanationSo which is the field you want to compare on? I see more than 1 fields in comparison hereALso which field need to update? Why are you using GROUP BY?? I cant see anything whch speaks about aggregation in your explanation.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|