logo
down
shadow

SQL Server throwing errors when trying to reference newly created column in already existing table


SQL Server throwing errors when trying to reference newly created column in already existing table

By : manoloesparta
Date : November 21 2020, 11:01 PM
Hope this helps When trying to reference/access newly added column to existing (or not?) table, I am always getting editor error and most of the time execution error (most != always, which is even weirder). , Just Put
code :
 ALTER TABLE Products ADD TotalSales INT NOT NULL CONSTRAINT Def DEFAULT 0
 Go -- added
 UPDATE P
 SET P.TotalSales = TEMP.TotalSalesComputed
 FROM Products P
 JOIN
(
     SELECT ProductID, SUM(OD.Quantity) TotalSalesComputed
     FROM [Order Details] OD
     GROUP BY ProductID
 ) TEMP
ON P.ProductID = TEMP.ProductID


Share : facebook icon twitter icon
How to get and compare existing table names with newly created table's name (PHP5)?

How to get and compare existing table names with newly created table's name (PHP5)?


By : Amith Tharayil
Date : March 29 2020, 07:55 AM
will be helpful for those in need Best way to do that is to query information schema DB. It contains all information about existing DBs and tables. But mysql 5 only.
code :
SELECT * 
FROM  `TABLES` 
WHERE  `TABLE_SCHEMA` =  'your_database_name'
AND  `TABLE_NAME` =  'your_table_name'
$result = mysql_query('SELECT * FROM  `TABLES` WHERE  `TABLE_SCHEMA` =  "your_database_name" AND  `TABLE_NAME` =  "'. $entry_name .'");
if (mysql_numrows($result) !== 0)
Adding rows to table with unique column: Get existing ID values plus newly-created ones

Adding rows to table with unique column: Get existing ID values plus newly-created ones


By : ZhengNL
Date : March 29 2020, 07:55 AM
help you fix your problem This first approach makes no assumptions about the behaviour of the JDBC driver when handling batch INSERTs. It avoids potential INSERT errors by
querying the table for any existing mail_id values in our current data set, makes note of the corresponding id value for those mail_id values that do exist, INSERTs the mail_id values that don't exist, and retrieves their (new) id values, and then inserts the rows in the other table (inv_table).
code :
try (Connection dbConn = DriverManager.getConnection(myConnectionString, "root", "usbw")) {
    dbConn.setAutoCommit(false);

    // test data and setup
    Long aid = 123L;
    List<MailidInvitation> invitationList = new ArrayList<MailidInvitation>();
    invitationList.add(new MailidInvitation(13L));
    invitationList.add(new MailidInvitation(11L));
    invitationList.add(new MailidInvitation(12L));
    // remove stuff from previous test run
    try (Statement s = dbConn.createStatement()) {
        s.executeUpdate("DELETE FROM mail_contacts WHERE mail_id IN (11,13)");
    }
    try (PreparedStatement ps = dbConn.prepareStatement(
            "DELETE FROM inv_table WHERE aid=?")) {
        ps.setLong(1, aid);
        ps.executeUpdate();
    }

    // real code starts here
    //
    // create a Map to hold `mail_id` and their corresponding `id` values 
    Map<Long, Long> mailIdMap = new TreeMap<Long, Long>();
    for (MailidInvitation a : invitationList) {
        // mail_id, id (id is null for now)
        mailIdMap.put(a.getId(), null);
    }

    // build an SQL statement to retrieve any existing values
    StringBuilder sb = new StringBuilder(
            "SELECT id, mail_id " +
            "FROM mail_contacts " +
            "WHERE mail_id IN (");
    int n = 0;
    for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
        if (n++ > 0) sb.append(',');
        sb.append(entry.getKey());
    }
    sb.append(')');
    String sql = sb.toString();

    // run the query and save the results (if any) to the Map
    try (Statement s = dbConn.createStatement()) {
        // <demo>
        System.out.println(sql);
        // </demo>
        try (ResultSet rs = s.executeQuery(sql)) {
            while (rs.next()) {
                mailIdMap.put(rs.getLong("mail_id"), rs.getLong("id"));
            }
        }
    }

    // <demo>
    System.out.println();
    System.out.println("mailIdMap now contains:");
    // </demo>

    // build a list of the `mail_id` values to INSERT (where id == null)
    //     ... and print the existing mailIdMap values for demo purposes
    List<Long> mailIdsToInsert = new ArrayList<Long>();
    for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
        String idValue = "";  // <demo />
        if (entry.getValue() == null) {
            mailIdsToInsert.add(entry.getKey());
            // <demo>
            idValue = "null";  
        } else {
            idValue = entry.getValue().toString();
            // </demo>
        }
        // <demo>
        System.out.println(String.format(
                "    %d - %s", 
                entry.getKey(),
                idValue));
        // </demo>
    }

    // batch insert `mail_id` values that don't already exist
    try (PreparedStatement ps = dbConn.prepareStatement(
            "INSERT INTO mail_contacts (mail_id) VALUES (?)", 
            PreparedStatement.RETURN_GENERATED_KEYS)) {
        for (Long mid : mailIdsToInsert) {
            ps.setLong(1, mid);
            ps.addBatch();
        }
        ps.executeBatch();
        // get generated keys and insert them into the Map
        try (ResultSet rs = ps.getGeneratedKeys()) {
            n = 0;
            while (rs.next()) {
                mailIdMap.put(mailIdsToInsert.get(n++), rs.getLong(1));
            }
        }
    }

    // <demo>
    System.out.println();
    System.out.println("After INSERT INTO mail_contacts, mailIdMap now contains:");
    for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
        System.out.println(String.format(
                "    %d - %s", 
                entry.getKey(),
                entry.getValue()));
    }
    // </demo>

    // now insert the `inv_table` rows
    try (PreparedStatement ps = dbConn.prepareStatement(
            "INSERT INTO inv_table (mid, aid) VALUES (?,?)")) {
        ps.setLong(2, aid);
        for (MailidInvitation a : invitationList) {
            ps.setLong(1, mailIdMap.get(a.getId()));
            ps.addBatch();
        }
        ps.executeBatch();
    }
    dbConn.commit();
}
SELECT id, mail_id FROM mail_contacts WHERE mail_id IN (11,12,13)

mailIdMap now contains:
    11 - null
    12 - 1
    13 - null

After INSERT INTO mail_contacts, mailIdMap now contains:
    11 - 15
    12 - 1
    13 - 16
// create a Map to hold `mail_id` and their corresponding `id` values 
Map<Long, Long> mailIdMap = new TreeMap<Long, Long>();
for (MailidInvitation a : invitationList) {
    // mail_id, id (id is null for now)
    mailIdMap.put(a.getId(), null);
}

// try INSERTing all `mail_id` values
try (PreparedStatement ps = dbConn.prepareStatement(
        "INSERT INTO mail_contacts (mail_id) VALUES (?)", 
        PreparedStatement.RETURN_GENERATED_KEYS)) {
    for (Long mid : mailIdMap.keySet()) {
        ps.setLong(1, mid);
        ps.addBatch();
    }
    int[] updateCounts = null;
    try {
        updateCounts = ps.executeBatch();
    } catch (BatchUpdateException bue) {
        updateCounts = bue.getUpdateCounts();
    }
    // get generated keys and insert them into the Map
    try (ResultSet rs = ps.getGeneratedKeys()) {
        int i = 0;
        for (Long mid : mailIdMap.keySet()) {
            if (updateCounts[i++] == 1) {
                rs.next();
                mailIdMap.put(mid, rs.getLong(1));
            }
        }
    }
}

// <demo>
System.out.println("mailIdMap now contains:");
// </demo>

// build a SELECT statement to get the `id` values for `mail_id`s that already existed
//     ... and print the existing mailIdMap values for demo purposes
StringBuilder sb = new StringBuilder(
        "SELECT id, mail_id " +
        "FROM mail_contacts " +
        "WHERE mail_id IN (");
int n = 0;
for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
    String idValue = "";  // <demo />
    if (entry.getValue() == null) {
        if (n++ > 0) sb.append(',');
        sb.append(entry.getKey());
        // <demo>
        idValue = "null";  
    } else {
        idValue = entry.getValue().toString();
        // </demo>
    }
    // <demo>
    System.out.println(String.format(
            "    %d - %s", 
            entry.getKey(),
            idValue));
    // </demo>
}
sb.append(')');
String sql = sb.toString();

// <demo>
System.out.println();
System.out.println(sql);
// </demo>
mailIdMap now contains:
    11 - 17
    12 - null
    13 - 19

SELECT id, mail_id FROM mail_contacts WHERE mail_id IN (12)
add values to newly added column of one existing table from column of another existing table

add values to newly added column of one existing table from column of another existing table


By : Pradeep Pol
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I am new to Mysql. I have created two tables Users and Administrations as follows: , TRUNCATE is a fine idea. But you can also update the table:
code :
UPDATE Administration a JOIN
       Users u
       ON a.id = u.id
    SET a.Lname = u.Lname
    WHERE u.Address = 'Brooklyn';
(C# - Excel) How do I write a formula to a newly created column for each existing row?

(C# - Excel) How do I write a formula to a newly created column for each existing row?


By : Agung Bezharie Hadin
Date : March 29 2020, 07:55 AM
I hope this helps you . Say you have an excel file with a bunch of numbers and rows, about 100 rows. , Edit:
You may want to try this:
code :
numberOfColumns = WS.UsedRange.Columns.CountLarge;
numberOfRows = WS.UsedRange.Rows.CountLarge;
Application excelApp = new Application();
excelApp.SheetsInNewWorkbook = 1;
////////////////////////////////////////////
//Add these lines to make it work???
////////////////////////////////////////////
try {
    excelApp.Visible = true;
}
catch {} 
////////////////////////////////////////////
Workbook excelWB = excelApp.Workbooks.Open(@"C:\test.xls", Type.Missing, false);
_Worksheet excelWS = excelWB.Sheets[1];
Range cellsRange = excelWS.UsedRange;
long LastCell = cellsRange.Columns.CountLarge + 1;
long numberOfRows = cellsRange.Rows.CountLarge;
excelWS.Cells[1, LastCell] = "Tax Formula";
for (int i = 2; i <= numberOfRows; i++)
{
    string niceFormula = "=SUM(L" + i + ",M" + i + ")*N" + i;
    excelWS.Cells[i, LastCell].Formula = niceFormula;
}
excelWB.Close(true);
excelApp.Quit();
//////////////////////////////////////////////////////////////////////////////////
//This function is absolute magic >.> - Fill DataTable with excel spreadsheet
//HDR=YES means "Spreadsheet has headers" Change to NO if not.
//name = "Log"; - This is the Sheet name to pull the data from
//////////////////////////////////////////////////////////////////////////////////
//oconn takes an SQL like command (Select Everything from name sheet) using con
//HDR=YES means that our data has headers :)
//////////////////////////////////////////////////////////////////////////////////
String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + copyToPath + ";Extended Properties='Excel 12.0 XML;HDR=YES;';";
OleDbConnection con = new OleDbConnection(constr);
OleDbCommand oconn = new OleDbCommand("Select * From [Log$]", con);
con.Open();
OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
DataTable data = new DataTable();
sda.Fill(data);
con.Close();
//////////////////////////////////////////////////////////////////////////////////
Subquery reference newly created column

Subquery reference newly created column


By : Moros
Date : March 29 2020, 07:55 AM
This might help you I have been trying to write a subquery based on the SQL Server docs. I am trying to write a SQL Server query to , Let us analyze your query first
code :
               select *  from (
                select *,cast (NULLIF(q5,'NULL') as int) as q5int 
                from ft_merge
                ) WHERE q5int=NULL
       Select * into #ft_merge
        from
        (

              Select 1 AS ID,'111' AS q5
              union
              Select 2, null
                  union
              Select 3,'2222'
                  union
              Select 4,null 
        )t



       /* using your query */  
       select *  from (
      select *,cast(q5 as int) as q5int 
        from #ft_merge where ISNUMERIC(q5) > 0 or q5 is null
      )t WHERE q5int is NULL
      /* another optimal way of doing this */

      select *,cast(q5 as int) as q5int 
      from #ft_merge where NULLIF(q5,NULL) is null    
Related Posts Related Posts :
  • What is the quickest way in Oracle SQL to find out if one or more duplicates exist in a table?
  • Oracle SQL: Dynamic timeframe calculation
  • Get rows from current month if older is not available
  • Is CAP theorem wrong?
  • Average and group by in SQL but for best 10 records only
  • using where in count sql
  • SQL - How create a select that makes search possible
  • Which is the best approach for creating Shopping cart for E-commerce site
  • How to define array/collection in stored procedure?
  • Why does my Access SQL VBA code jump out of the Sub?
  • MS SQL - Conditional UPDATE return result
  • SQL Server - How to check if string is text or any representation of 0?
  • How do I create this output in SQL
  • Optimizing rank() query
  • Get the information in terms of time about people who are free on a particular day
  • Find the month in which maximum number of employees hired
  • Count posts threads comments but also return those with no threads
  • Count consecutive NULL values for given ID
  • How to create an integer from a certain string cell in PSQL
  • SQL - Using SELECT in the WHERE clause of another SELECT
  • Find the gap in ID column + select the prev/next date column
  • EXEC an INSERT with a dynamic Schema TSQL SSMS
  • Try optimize a specific SQL query
  • Importing excel to access with wildcards
  • How to select 1st and 2nd row instance in SQL table having the same value in another table?
  • Invalid Operation Exception when filling DataSet
  • Select max from calculated column alongside other columns
  • Selecting non duplicates from distint query
  • How to stop a single database in an sql instance?
  • Oracle error:missing right parenthesis
  • Non-Equi Self Join
  • Combine two tables for one output with separation on different columns
  • Select the used and not used Rows from Foreign key constraints
  • SQL stored procedure, Column calculated from two other calculated columns
  • Default to a value when a sub-query statement fails?
  • How do I do an SQL query based on a foreign key field?
  • How can you have two COUNT queries GROUPED BY the same column in SQL?
  • How to select only the second max date from a table
  • how to get previous 11 month names from sysdate using oracle
  • figuring out how many block accesses are needed
  • Calculate difference of dates belonging to same group in sql
  • ERROR Message: ORA-00923: FROM keyword not found where expected
  • Oracle SQL statement to update column values based on specific condition
  • SQL Concatenation Query - Four As in Concatenated Name
  • When utilizing a microservices architecture, will the underlying read/write database become a bottleneck?
  • SQLite GROUP BY on result of union not using indexes of constituent tables
  • Prevent duplicates for a certain GraphCool model
  • How to create custom, dynamic string sequence in SQL Server
  • Finding entry page, exit page and bounces -sql
  • Complex Self-Join
  • Connected Components
  • SQL Queries Processing order is not consistent
  • Find 3rd highest cost from table
  • How to log queries written in eclipse BIRT
  • Why does round(143.23,-1) return 140?
  • Find highest value in column using ANY or ALL
  • Need SQL query for hierarchical data in a flat result set
  • How to design a table hierarchy for orders with different products parameters
  • ms Access sql expand
  • PostgreSQL: operator does not exist: timestamp without time zone == timestamp without time zone
  • shadow
    Privacy Policy - Terms - Contact Us © soohba.com