Getting 0 rows returned on query

so let me start will the basic table layout for all tables involved:

#zip_code_time_zone

+----+----------+-----------+ | id | zip_code | time_zone | +----+----------+-----------+ | 1 | 00544 | -1 | | 2 | 00601 | -3 | | 3 | 00602 | 0 | | 4 | 00603 | -3 | | 5 | 00604 | 0 | +----+----------+-----------+

#pricing_record

+------+---------------+--------------------+ | id | location_code | service_center_zip | +------+---------------+--------------------+ | 7119 | TX725 | 79714 | | 7121 | TX734 | 75409 | | 7122 | TX737 | 78019 | | 7124 | TX742 | 75241 | | 7126 | TX751 | 77494 | +------+---------------+--------------------+

#transaction_record

+----+-----------------+------------------+--------------+--------------+ | id | truck_stop_code | create_date | gps_verified | central_time | +----+-----------------+------------------+--------------+--------------+ | 1 | CA428 | 05/01/2015 14:52 | 0 | NULL | | 2 | CA343 | 05/01/2015 19:10 | 0 | NULL | | 3 | CA223 | 05/01/2015 09:28 | 0 | NULL | | 4 | CA721 | 05/01/2015 07:55 | 0 | NULL | | 5 | MN336 | 05/01/2015 06:46 | 0 | NULL | +----+-----------------+------------------+--------------+--------------+

When I was working on this project an issue was noticed with the create_date column in transaction_record. It needs to be converted to central time, so I wrote an update query, but I have been unable to successfully set the central_time column. My query is below:

query

UPDATE t SET t.central_time = DATEADD(hour, z.time_zone,CONVERT(DATETIME, t.create_date, 120)) FROM eagle_devel.dbo.zip_code_time_zone z INNER JOIN eagle_devel.dbo.pricing_record p ON z.zip_code = p.service_center_zip INNER JOIN eagle_devel.dbo.transaction_record t ON t.truck_stop_code = p.location_code

This is what i get when I run the query

(0 row(s) affected)

NOTES

The time_zone column in #zip_code_time_zone is not the standard UTC it is the difference to calculate to central

I am still working on this as we speak, just looking for some extra assistance to see if someone else can fix it faster than myself.

--------------Solutions-------------

Try like this instead with little changes, table you are updating should be in FROM clause and then adjust the JOIN accordingly

UPDATE t
SET t.central_time = DATEADD(hour, z.time_zone,CONVERT(DATETIME, t.create_date, 120))
FROM eagle_devel.dbo.transaction_record t
INNER JOIN eagle_devel.dbo.pricing_record p ON t.truck_stop_code = p.location_code
INNER JOIN eagle_devel.dbo.zip_code_time_zone z ON z.zip_code = p.service_center_zip

Category:sql Time:2018-11-06 Views:0
Tags: sql sql server

Related post

  • Getting row number for query 2012-02-05

    I have a query which will return one row. Is there any way I can find the row index of the row I'm querying when the table is sorted? I've tried rowid but got #582 when I was expecting row #7. Eg: CategoryID Name I9GDS720K4 CatA LPQTOR25XR CatB EOQ21

  • ADO cannot get rows from access query 2014-04-01

    Hi I am developing an application in Delphi which needs to get data from an Access database. My problem is that I can open tables, by query, table or dataset methods, and I can get rows from queries, but only when the row set is < 1200 records. Fo

  • How do I execute a stored procedure once for each row returned by query? 2009-05-20

    I have a stored procedure that alters user data in a certain way. I pass it user_id and it does it's thing. I want to run a query on a table and then for each user_id I find run the stored procedure once on that user_id How would I write query for th

  • does the number of row returns in query with order by affects performance in oracle? 2012-03-04

    I have a simple query like this: select * from ( select this_.id as y0_, this_.tempurature as y1_, this_.pressure as y2_ from mytable this_ where this_.start_time_local>=to_date('02/03/2012', 'MM/DD/YYYY') and this_.activity_type_pk<>12 and

  • Enforce only single row returned from DataReader 2011-10-20

    I seem to write this quite a lot in my code: using (var reader = cmd.ExecuteReader()) { if (reader.Read()) { result = new User((int)reader["UserId"], reader["UserName"].ToString()); } if (reader.Read()) { throw new DataException("multiple rows return

  • Display message if nothing returned from Query 2012-03-20

    How do I display a message if nothing is returned from my query? I tried this: while($info2 = mysql_fetch_assoc( $data2 )) { // la la la lots of code here } else { echo "Nothing Returned"; } Only to get this error: Parse error: syntax error, unexpect

  • How do you get the row returned by a SQL query that includes PREPARE and EXECUTE in PHP? 2012-04-11

    How do you get the row returned by this SQL query using PHP: SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*) FROM mytable))); SET @sql := CONCAT('SELECT * FROM mytable LIMIT ', @r, ', 1'); PREPARE stmt1 FROM @sql; EXECUTE stmt1; Setting $query equa

  • How can I find out how many rows a MySQL query returns in Java? 2009-01-19

    How can I find out how many rows a MySQL query returns in Java? --------------Solutions------------- From the jdbc faq: .18. There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set? No, b

  • How to get only second,third,fourth or fifth row of a query result?(SQL SERVER 2000) 2009-02-11

    I would need a suggestion or even a solution, how to get only the second,third... row of a query result with high performance. I know there is the possibility of row_number(SQL Server 2005 or higher) but not for SQL Server 2000 :-( My first try to ge

  • MySQL query works in phpmyadmin but no rows returned in PHP result set 2010-01-27

    I have the following MySQL query: SELECT SQL_CALC_FOUND_ROWS p.* FROM product AS p LEFT JOIN productCategory AS c ON FIND_IN_SET(c.id, REPLACE(TRIM(p.categories), ' ',',')) WHERE ( c.id IS NULL OR c.status = 'D' OR p.categories IS NULL OR TRIM(p.cate

  • Why is Wordpress $wpdb->query() response 1 when the rows returned is 0 2010-06-07

    I'm doing the following in a custom function: $exists = $wpdb->query($wpdb->prepare(' SELECT COUNT(*) FROM wp_%d_gdsr_data_article WHERE post_id = %d ', $blog_id, $post_id)); $exists evaluates to 1 even if no rows are returned by the query. Als

  • Any way to get the amount of rows returned using mysqli prepared statements? 2011-01-05

    Wondering if there is a way to get the amount of rows returned from a query using mysqli prepared statements, sort of like mysql_num_rows($query); --------------Solutions------------- Presuming you're doing something like: $result = $statement->ex

  • Codeigniter get number of row from search query 2011-11-01

    I have a query in my controller which searches for the term/keyword that is in the url. Example /search/keyword Then through my controller I perform the search doing: $viewdata['search_results'] = $this->Search_model->search(strtolower($this-

  • Get Number of Rows returned by ResultSet in Java 2011-11-28

    I have used a ResultSet that returns certain number of rows. My code is something like this: ResultSet res = getData(); if(!res.next()) { System.out.println("No Data Found"); } while(res.next()) { // code to display the data in the table. } Is there

  • How do I count the total number of rows returned from a WQL query? 2011-11-30

    I am trying to find the total number of CPUs on a computer via get-wmiobject. The query: select * from win32_processor returns more than 1 row if there is more than 1 processor on the computer. WQL doesn't have the count keyword like in TSQL. So I am

  • mySQL database query LIMIT help- How to create pages if number of rows returned exceeds 'x'? 2012-04-09

    I'm attempting to write a script that returns rows from a mySQL database. Basically, the users on my site receive "experience points" and go up "ranks" based on the number of experience points they get. I have a page called "recentexp.php" where all

  • Sql query to return SUM and # of rows that match query 2009-05-21

    Table UserData (UserID, Sales, Credits) I need to return the SUM of sales, sum of credits and the # of rows returned for a given date range. Is it possible in 1 query? --------------Solutions------------- SELECT COUNT(*), SUM(Sales), SUM(Credits) FRO

  • get row no through mysql query 2009-09-14

    hello sir How to get the row no through mysql query for each row? (i want to display sno) Please help me sir --------------Solutions------------- I believe you are looking to see if you can get the row number for each row through MySQL query right? h

  • select top 5 rows returned by particular query sql server 2010-07-08

    I want to retrieve top 5 rows returned by this query. How to do this select COUNT(trippackageID), trippackageid from tbl_holiday_booking group by trippackageID --------------Solutions------------- You are not specifying the order, do you want the pac

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 2.444 (s). 14 q(s)