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 |
|
tcp
Starting Member
3 Posts |
 Posted - 2007-11-13 : 22:57:02
|
| Hi,I have a table which has AirlineCode and FlightNumber columns. I have created a new column called Flight which I would like to have as a combined data (AirlineCode+FlightNumber) for each row in this table.For example, the first row may have AA and 71 and currently Flight is null. The second row has US and 2824 with Flight being null. After this query executes, I would like the Flight column to hold AA71 for the first row and US2824 for the second row etc.Thanks |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-13 : 23:29:00
|
| update table set Flight = AirlineCode + FlightNumberBut why do that. It de-normalizes the table. |
 |
|
|
tcp
Starting Member
3 Posts |
Posted - 2007-11-14 : 00:09:07
|
| I'm looking to create one column which is unique in the table (actually AirlineCode/FlightNumber is not unique...have to also combine in depart and arrive city codes to make unique). The reason that I would like to do this is because I want to do an IN from a FlightResult set. So, I have a flight result set that gives me a set of unique keys and then I need added value info from this table for each result. So I want to do something like select * from FlightRouteInfo where flight in (US2824, AA4223, AA71, BA4285)but the number of routes can be fairly large (more than 200 as opposed to the 4 shown above).Maybe you have some other advise to make this better.Anyway, a followup question, when I use your update by doing this:update FlightRouteStats set Flight = AirlineCode+cast(FlightNumber as varchar)I end up with 'AA 71' How to run the update with no space between the concat'd fields?Thanks |
|
|
|
tcp
Starting Member
3 Posts |
Posted - 2007-11-14 : 00:37:39
|
| Forget the last question...it is silly...because AirlineCode declared char(3) for some reason... |
|
|
|
|
|
|
Select and Update Combining Data