CODEWITHSUNDEEP
pythonsql-serverpandas
Mahmoud Al-Haroon
Mahmoud Al-Haroon

Reputation: 2449

How to create query that's update columns value using sql server or pandas function even

Now I have a table something like the below table:

esn_missing_in_DF_umts

|---------------------|------------------|---------------------|------------------|------------------|------------------|
|      cell_name      |     n_cell_name  |    source_vendor    |    target_vendor |     source_rnc   |     target_rnc   |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         1           |         8        |         x           |         y        |                  |                  |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         2           |         5        |         x           |         x        |                  |                  |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         3           |         6        |         x           |         x        |                  |                  |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         4           |         9        |         x           |         y        |                  |                  |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         5           |         10       |         x           |         y        |                  |                  |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         6           |         11       |         x           |         y        |                  |                  |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         7           |         12       |         x           |         y        |                  |                  |
|---------------------|------------------|---------------------|------------------|------------------|------------------|

Now I have two columns are empty in sqlServer or dataframe the source_rnc and the target_rnc:

Here's the other two tables I want to update the two columns from

esn_umts_intra_sho

|---------------------|------------------|------------------|
|      ucell          |    urelation     |     ucell_rnc    |
|---------------------|------------------|------------------|
|         13          |         5        |         abc567   |
|---------------------|------------------|------------------|
|         8           |         6        |         abc568   |
|---------------------|------------------|------------------|
|         14          |         8        |         abc569   |
|---------------------|------------------|------------------|
|         7           |         9        |         abc570   |
|---------------------|------------------|------------------|
|         16          |        10        |         abc571   |
|---------------------|------------------|------------------|
|         5           |        11        |         abc572   |
|---------------------|------------------|------------------|
|         17          |        12        |         abc573   |
|---------------------|------------------|------------------|
|         10          |         9        |         abc574   |
|---------------------|------------------|------------------|
|         9           |        17        |         abc575   |
|---------------------|------------------|------------------|
|         12          |        11        |         abc576   |
|---------------------|------------------|------------------|
|         11          |        12        |         abc577   |
|---------------------|------------------|------------------|

df_umts_carrier

|---------------------|------------------|
|      cell_name_umts |     rnc          |
|---------------------|------------------|
|         1           |         xyz123   |
|---------------------|------------------|
|         2           |         xyz124   |
|---------------------|------------------|
|         3           |         xyz125   |
|---------------------|------------------|
|         4           |         xyz126   |
|---------------------|------------------|
|         5           |         xyz127   |
|---------------------|------------------|
|         6           |         xyz128   |
|---------------------|------------------|
|         7           |         xyz129   |
|---------------------|------------------|

So Not I want to update the source_rnc and target_rnc through those two tables esn_umts_intra_sho and df_umts_carrier

So I imagine that the query could be like this

UPDATE [toolDB].[dbo].[esn_missing_in_DF_umts]
 SET [toolDB].[dbo].[esn_missing_in_DF_umts].[target_rnc] = CASE WHEN [toolDB].[dbo].[esn_missing_in_DF_umts].[target_vendor] = 'HUA' THEN [toolDB].[dbo].[df_umts_carrier].[rnc]
 FROM [toolDB].[dbo].[esn_missing_in_DF_umts]
  INNER JOIN [toolDB].[dbo].[df_umts_carrier] 
  ON [n_cell_name] = [cell_name_umts] 
ELSE
 UPDATE [toolDB].[dbo].[esn_missing_in_DF_umts] 
  SET [toolDB].[dbo].[esn_missing_in_DF_umts].[target_rnc] = [toolDB].[dbo].[esn_umts_intra_sho].[ucell_rnc]
  From [toolDB].[dbo].[esn_missing_in_DF_umts] INNER JOIN [toolDB].[dbo].[esn_umts_intra_sho] 
  ON [n_cell_name] = [ucell]

I want the final output to be somthing like this:

|---------------------|------------------|---------------------|------------------|------------------|------------------|
|      cell_name      |     n_cell_name  |    source_vendor    |    target_vendor |     source_rnc   |     target_rnc   |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         1           |         8        |         x           |         y        |        xyz123    |   abc568         |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         2           |         5        |         x           |         x        |        xyz124    | xyz127           |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         3           |         6        |         x           |         x        |        xyz125    |   xyz128         |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         4           |         9        |         x           |         y        |        xyz126    |   abc575         |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         5           |         10       |         x           |         y        |        xyz127    |  abc574          |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         6           |         11       |         x           |         y        |        xyz128    |   abc576         |
|---------------------|------------------|---------------------|------------------|------------------|------------------|
|         7           |         12       |         x           |         y        |        xyz129    |  abc577          |
|---------------------|------------------|---------------------|------------------|------------------|------------------|

I tried even with pandas but doesn't work... I wish someone help me.

Upvotes: 0

Views: 39

Answers (1)

Yarner
Yarner

Reputation: 115

The best thing is to make the query as if you were writing a SELECT statement with the Case clause in it. Once it works as expected, you can amend it for your update.

So in this example, if the main tables Column = bla, then get the data from the first joined table, else the other table.

Quick amendment Make sure its all rows you are happy to update, else remember to put in a where statement. That's why its best to work out your logic in a SELECT and move on from there.

I think you want something like this:

UPDATE  [toolDB].[dbo].[esn_missing_in_DF_umts] 
SET [toolDB].[dbo].[esn_missing_in_DF_umts].[target_rnc] = (CASE WHEN UMT.target_vendor = 'HUA' THEN carrier.rnc ELSE SHO.ucell_rnc END )
FROM [toolDB].[dbo].[esn_missing_in_DF_umts] UMT
LEFT JOIN [toolDB].[dbo].[df_umts_carrier] carrier ON  UMT.n_cell_name = carrier.cell_name_umts
LEFT JOIN [toolDB].[dbo].[esn_umts_intra_sho] SHO   ON UMT.n_cell_name = SHO.ucell

Upvotes: 1

Related Questions