I want to use CASE statement to update some records in sql server 2005
Add a WHERE clause
sql
Cannot simply use PostgreSQL table name (“relation does not exist”)
From what I’ve read, this error means that you’re not referencing the table name correctly. One common reason is that the table is defined with a mixed-case spelling, and you’re trying to query it with all lower-case. In other words, the following fails: Use double-quotes to delimit identifiers so you can use the specific mixed-case … Read more
How to SUM and SUBTRACT using SQL?
I think this is what you’re looking for. NEW_BAL is the sum of QTYs subtracted from the balance: If you want to update the item balance with the new balance, use the following: This assumes you posted the subtraction backward; it subtracts the quantities in the order from the balance, which makes the most sense … Read more
Remove duplicate rows in MySQL
A really easy way to do this is to add a UNIQUE index on the 3 columns. When you write the ALTER statement, include the IGNORE keyword. Like so: This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error out. As always, you may want to take a backup before running something like this…
Arithmetic overflow error converting numeric to data type numeric
My guess is that you’re trying to squeeze a number greater than 99999.99 into your decimal fields. Changing it to (8,3) isn’t going to do anything if it’s greater than 99999.999 – you need to increase the number of digits before the decimal. You can do this by increasing the precision (which is the total number of … Read more
Count(*) vs Count(1) – SQL Server
There is no difference. Reason: Books on-line says “COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )“ “1” is a non-null expression: so it’s the same as COUNT(*). The optimizer recognizes it for what it is: trivial. The same as EXISTS (SELECT * … or EXISTS (SELECT 1 … Example: Same IO, same plan, … Read more
SQL ZOO List each continent and the name of the country that comes first alphabetically
when you use x.name=y.name you are comparing country name from x with the country name from y if both instances have the same country name. That basically would just return you the full table x. You want to use x.continent=y.continent because you only want to compare the country name of an instance from x with the country name of … Read more
SQL array flattening: Why doesn’t CROSS JOIN UNNEST join every nested value with every row?
The best way to think about this is by looking at what happens on a row-by-row basis. Setting up some input data, we have: (I’m using a third element for the second row to make things more interesting). If we just select from it, we get output that looks like this: Now let’s talk about … Read more
Oracle SQL query for Date format
to_date() returns a date at 00:00:00, so you need to “remove” the minutes from the date you are comparing to: You probably want to create an index on trunc(es_date) if that is something you are doing on a regular basis. The literal ’27-APR-12′ can fail very easily if the default date format is changed to anything different. So make sure … Read more
How to join two tables by multiple columns in SQL?
Yes: You can use Inner Join to join on multiple columns. Create table Insert values